assign a clickable macro to a cell?
 

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.


---
Message posted from http://www.ExcelForum.com/

assign a clickable macro to a cell?
 

Hi
you'll need an event procedure for this (e.g. the worksheet_change
event). See the following site for more details about this:
http://www.cpearson.com/excel/events.htm

--
Regards
Frank Kabel
Frankfurt, Germany


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.


---
Message posted from http://www.ExcelForum.com/

assign a clickable macro to a cell?
 

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

--
Message posted from http://www.ExcelForum.com

assign a clickable macro to a cell?
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


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

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

etc etc for about 30 cells

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


---
Message posted from http://www.ExcelForum.com/

assign a clickable macro to a cell?
 

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

--
Message posted from http://www.ExcelForum.com

assign a clickable macro to a cell?
 

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

In article ,
neowok wrote:

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

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

etc etc for about 30 cells

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

assign a clickable macro to a cell?
 

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

--
Message posted from http://www.ExcelForum.com

assign a clickable macro to a cell?
 

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.


In article ,
neowok wrote:

ok thanks ill try that.

still having troulbe getting my simple bit fo code to work without
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 code
above seems to ignore it even if i select CA7. if i uncomment the
combobox1_click line then it brings up an error on every cell clicked,
highlighting "Private Sub Worksheet_SelectionChange(ByVal Target As
Range)"in yellow and combobox1_click in grey and the error again saus
sub or function not defined. no idea wots going on.

assign a clickable macro to a cell?
 

In windows you can if you make it public.

--
Regards,
Tom Ogilvy

"JE McGimpsey" wrote in message
...
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.


In article ,
neowok wrote:

ok thanks ill try that.

still having troulbe getting my simple bit fo code to work without
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 code
above seems to ignore it even if i select CA7. if i uncomment the
combobox1_click line then it brings up an error on every cell clicked,
highlighting "Private Sub Worksheet_SelectionChange(ByVal Target As
Range)"in yellow and combobox1_click in grey and the error again saus
sub or function not defined. no idea wots going on.

assign a clickable macro to a cell?
 

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...

In article ,
"Tom Ogilvy" wrote:

In windows you can if you make it public.

assign a clickable macro to a cell?
 

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

--
Message posted from http://www.ExcelForum.com