Activate VBA code from specific cells on data entry

[Magnetar]

Evening all!

Am very new to Excel VBA, & need help urgently in the following area (please!). On clicking into a range of cells, need to activate a MsgBox.

Example of this is range A40:A50, within worksheet1.

Example of code under worksheet1 is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Sheet1.Range(A39, A43) Then

StaffCosts

End If

End Sub

Code will then call a sub/function to display message, assub/function to display message, as per following example:

Sub StaffCosts()

MsgBox "[Message]", vbInformation + vbOKOnly, "[Title of Message to Users]"

End Sub

As mentioned above am very, very new to Excel VBA. Basically I would like to know the Excel equivalent of the appropriate event for the cell ranges upon which to trigger the above message from code, (similar to events on forms within MS Access).

Further note: have successfully managed to manually run the code from the Tools | Macro | Macros...|Run menu.

However I want to trigger the msgbox each time the user selects a value from a drop-down list, and enters this specifically into cells A40 to A50, for example.

Would really appreciate some help with this very soon. Many, many thanks in advance.

Kind regards, - Magnetar


[Ps: Loomah, many thanks for your response earlier today, (which I mistakenly submitted the above in the incorrect forum beforehand!!!).
I did try your suggestion:

if target.address = "$A$40:$A$50" then
call othersub
end if

- but was unable to trigger the MsgBox. Open to any further suggestions/solutions. Many thanks in advance. Magnetar].

 

[PHV]

Take a look at Intersect in the Object browser and the Excel VBA help.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

The event you are looking for is the WORKSHEET_CHANGE_EVENT
You can get to this by right clicking on your sheet tab and choosing "View Code"
Then choose "Worksheet" from the left side dropdown and "Change" form the right side. You need to put your code in the sub that has been generated

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Loomah]

Magnetar
I may have got yhe wrong end of the stick before as I thought you were trying to call the second sub if the whole range A40:A50 was selected.

I'm guessing that's why my code didn't work the way you were expecting!!!

I would have got back earlier but new job, new browsing policy so no more long hours on TT for me for a while:-(

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​