If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > activate macro when cell is selected

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 5
activate macro when cell is selected

hello eveyone.
im trying activate a macro when a cell is selected.
for example:

i have a range of cells a1 d10.
if any cell within this range is selected i would like for a macro to be run automatically.

when cell is selected a box would come up with options of selecting 1-6.
value of selected cell would copy to cell assign to option.

1=a20
2=a21
3=a22
4=a23
5=a24
6=a25

thanks
Reply With Quote
  #2 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
This took me about 2 days to find it out

here is the trick
ALT+f11 get the VB thing
the in Excel Objects
double click on the said sheet

change the drop down boxes to worksheet and Change

you show see a

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub

what I did was create a select case on the Target

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address

CASE "$D$10"
call that_other_sub
Case "$E$16", "$E$17", "$E$18", "$E$19", "$E$20", "$E$21", "$E$23"
call and_other_sub
case else
end select
End Sub

you make you user very happy when you get it working
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE

Last edited by myle; 11-27-12 at 21:09. Reason: spelling
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 5
thanks for replying.
i tried this but wont even run anyone with any suggestion?
thanks
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 5
this is a start.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then <---i need this to be a range a1:g10
MsgBox "Cell " & Target.Address & " has changed."

End If
End Sub
Reply With Quote
  #5 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
Quote:
Originally Posted by carlmart711 View Post
thanks for replying.
i tried this but wont even run anyone with any suggestion?
thanks
what happen

I found this

excel - VBA test if cell is in a range - Stack Overflow

and tested it it work
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE
Reply With Quote
  #6 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
Here the file I created base on the that link

it a nice trick MMMM think i can use this on my own staff
Attached Files
File Type: zip PlayinRange.zip (6.7 KB, 3 views)
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE

Last edited by myle; 11-28-12 at 00:43.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On