Call Sub when cell is double clicked

[MarkNumskull]

Hi,

Iv trawled for solutions to this for ages, so far the only one that works is flawed as i double click any cell in the worksheet and it calls the sub.

What im trying to do is when a validation list is changed (ie from Mark to David) it calls the sub that then in turn opens the worksheet with the name David. My sub that calls the worksheet is fine and works no problem. The problem im having is with the sub workbook_change procedure. My cell with the validation list is actually 4 Merged cells but haas the value of b1.

Does anyone have any suggestions with this? I have the Workbook_change in the worksheet code window and have tried everything (as far as i know!)

Many Thanks,

Mark

[GTO]

Hi Mark,

What is not happening when using the change event? This seems to work:

[vba]
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1")) Is Nothing _
And Not Target.Count > 1 Then
Call Demo(Target.Value)
End If
End Sub

Private Sub Demo(sVal As String)
MsgBox "Do something with " & sVal
End Sub
[/vba]

Mark

[MarkNumskull]

Thanks for the reply but doesn't seem to work either.

Iv attached the worksheet im working on so you can see what im trying to do.

in each sheet at the top beside GO TO is a validation list, when this is changed to the name the corresponding worksheet opens ( in theory!)

Thanks Alot

Mark

[GTO]

Hey.... you said it was in B1 - that's B2!

[MarkNumskull]

Hahaha, just keepin you on your toes GTO! Sorry about that, i meant B2, my heads up my bum today!

[MarkNumskull]

BTW the above works absolutly perfectly now GTO! Thanks alot! I dont really understand how it works (cus iv never used this procedure before) but it does!

[MarkNumskull]

Actually, yknow the way that is in a worksheets code, is there anyway to make it work accross the workbook instead of just in each sheet? The reason is people may come and go and the people using the sheet wouldn't have the know how to update the code for a new staff member?

[GTO]

Sorry 'bout that. I would have commented the code.

.Intersect returns the intersection of two or more ranges. In a blank wb. try:

[vba]
Sub exaIntersect()
Dim rng As Range
Set rng = Application.Intersect(Range("A1:B10"), Range("B5:C15"))

MsgBox rng.Address

End Sub
[/vba]

Now in the code we used, we used If Not ... Is Nothing, as if Target and Range("B2") do not intersect, Nothing will be returned.

In essence, if a range IS returned,and we combine this with Not Target.Count > 1, we ensure that B2 and only B2 has just been cahnged.

Hope that helps,

Mark

[MarkNumskull]

Ah brilliant, that makes sense, thanks again. I don't know what i would do without these forums sometimes!

[GTO]

Reference #7:

You could copy the code to your template sheet's module, as I assume that as an employee is added, that sheet is copied and renamed, right?

You could also have the code only in one place, that being in the ThisWorkbook Module and using something like:
[vba]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Target, Sh.Range("B2")) Is Nothing _
And Not Target.Count > 1 _
And Sh.Range("I1").Value = "EmpSheet" Then
Call Demo(Target.Value)
End If
End Sub

Private Sub Demo(sVal As String)
MsgBox "Do something with " & sVal
End Sub
[/vba]

...with of course the private Demo sub being replaced by your public sub in a standard module. The third test I added would check a hidden cell. This way, changing B2 on lets say 'Master Table' wouldn't cause the code to fire.

mark