assign a clickable macro to a cell?

N

neowok

Basically what I want to do is make it so that when a user clicks the
cell on sheet1 (kinda like a hyperlink), excel goes over to sheet2, and
selects a specific static index from a dropdown (which will then run
some other code that ive got going normally for when an item in that
combobox is clicked).

Im sure its possible and isnt too hard, I just havent done anything
like that before.
 
N

neowok

looks like i can probably use the workbook_selectionchange event and i
there say something like

if target = cell A4 then go to sheet2 and select xx value from th
combobox
if target = cell B4 then go to sheet2 and select xx value from th
combobox
if target = cell B5 then go to sheet2 and select xx value from th
combobox

etc etc for about 30 cells

just wondering if that would slow everything down a lot since it woul
be executing all that every single time any new cell is selected
 
F

Frank Kabel

Hi
if you restrict the range which you process in your macro it should be
o.k. I have such procedures in place and normally you wouldn't notice
the execution delay
 
N

neowok

hmm well the range will only be 30 specific cells which will remain th
same so thats 30 if statements executed every time a new cell i
selected :/ ill have to try it and see how slow it makes it, i do lik
to move around quite fast.

just trying to work out at the moment why im getting an erro saying su
or function not defined when i have the following code in sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.address = "$CA$7" Then
Worksheets("3.data").Select
ComboBox1.ListIndex = 25
ComboBox1_Click
End If
End Sub

it gives me the rror for the combobox1_click line, basically i want i
to select the correct item from the list (by setting the index to 25
and then make it think that item 25 was clicked so it performs th
click event as if it had been clicked normally. I did make th
combobox1_click public but its still giving me the error on that line.
the combobox1_click event is on the "3.data" sheets code, and thi
sheet gets selected in my above code before its trying to execute th
code for the click anyway
 
J

JE McGimpsey

30 cells probably won't be noticeable, but you can use Intersect to
prescreen Target to make it more efficient:

Private Sub Worksheet_SelectionChange(ByVal Fred As Excel.Range)
With Fred
If .Count > 1 Then Exit Sub 'more than one cell selected
If Not Intersect(.Cells, Range("A4,B4:B5,C15:J15")) _
Is Nothing Then
Select Case .Address(False, False)
Case "A4"
'do your selection
Case "B4"
'do your selection
'etc
End Select
End If
End With
End Sub
 
N

neowok

ok thanks ill try that.

still having troulbe getting my simple bit fo code to work withou
errors

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "CA7" Then
Worksheets("3.data").Select
ComboBox1.ListIndex = 25
'ComboBox1_Click
end If
End Sub

first off i cant get it to work when the cell selected is CA7, the cod
above seems to ignore it even if i select CA7. if i uncomment th
combobox1_click line then it brings up an error on every cell clicked
highlighting "Private Sub Worksheet_SelectionChange(ByVal Target A
Range)"in yellow and combobox1_click in grey and the error again sau
sub or function not defined. no idea wots going on
 
J

JE McGimpsey

You can't call an event macro (ComboBox1_Click) from a sub - it's fired
automatically when the combobox is clicked.

If you want to call the same code as when the combobox is clicked, put
the code in a separate sub in a regular code module, and call it from
both your Worksheet_SelectionChange and the ComboBox1_Click macros.
 
J

JE McGimpsey

Not sure what I was thinking - except I obviously confused my own rule,
(i.e., "shouldn't") with "can't".

Of course, you *can't* call an ActiveX control's event macro on a Mac...
 
N

neowok

combobox1_click was public but on a different sheet than th
selectionchange, but the code switches to the sheet containin
combobox1 before i try to call the procedure so even that shouldnt mak
a difference. for some reason it still didnt work, and there must b
something wrong with my code because selectionchange isnt firing when
select the given cell, in this case CA
 
Top