didijaba
Well-known Member
- Joined
- Nov 26, 2006
- Messages
- 511
Hi,
I'm having trouble using public declared variable from Worksheet Selection Change event to macro in Module1.
THis is code I have. Thanks for any advice you can spare.
I'm having trouble using public declared variable from Worksheet Selection Change event to macro in Module1.
THis is code I have. Thanks for any advice you can spare.
Code:
Option Explicit
Public XXX As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CodeCall As String
If Application.Or(Target.Rows.Count > 1, Target.Columns.Count > 1) Then
Exit Sub
End If
Select Case Target.Column
Case 2
If IsEmpty(Target) = False Then
XXX = "Sheet24"
CodeCall = "'" & ActiveWorkbook.Name & "'!" & Target.Offset(0, 9).Text
Application.Run CodeCall
End If
Case 5
If IsEmpty(Target) = False Then
XXX = "Sheet25"
CodeCall = "'" & ActiveWorkbook.Name & "'!" & Target.Offset(0, 7).Text
Application.Run CodeCall
End If
Case 8
If IsEmpty(Target) = False Then
XXX = "Sheet31"
CodeCall = "'" & ActiveWorkbook.Name & "'!" & Target.Offset(0, 5).Text
MsgBox CodeCall
Application.Run CodeCall
End If
End Select
End Sub
Code:
Option explicit
Sub dannyrolnickevent(XXX)
Sheet2.Range("B6:H14").Value = XXX.Range("L2:R10").Value
Call SubRoutine_01
End Sub