I would understand what you need better if you posted a small sample workbook, with a short description of what is supposed to happen inside the workbook.
You can dynamically have user's select a Cell. See the attached file with complete code that follows that selects a 'Special Cell' when the User 'Double Clicks' that cell.
I hope this makes things clearer and not more confusing.
Lewis
ThisWorkbook Code:
Option Explicit
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'Return focus of the 'Double Clicked' Cell to the user
Cancel = True
'Process the 'Double Click Event'
Call ProcessDoubleClickEvent(Sh, Target)
End Sub
Ordinary Module 'ModSpecialCell' Code:
Option Explicit
Private myGblSheetName As String
Private myGblCellAddress As String
Sub ProcessDoubleClickEvent(ByVal Sh As Object, ByVal Target As Range)
'Get the Sheet Name of the Double Clicked Cell
myGblSheetName = Sh.Name
'Get the Cell address of the Double Clicked Cell (without '$' signs)
myGblCellAddress = Target.Address(False, False)
MsgBox "Special Cell Selected." & vbCrLf & _
"Sheet Name: " & myGblSheetName & vbCrLf & _
"Cell Address: " & myGblCellAddress
End Sub
Sub SomeLaterProcessing()
If Len(myGblSheetName) > 0 Then
MsgBox "Processing with User Special Cell." & vbCrLf & _
"Sheet Name: " & myGblSheetName & vbCrLf & _
"Cell Address: " & myGblCellAddress
Else
MsgBox "Processing Not Allowed." & vbCrLf & _
"User must first 'Double Click' a Special Cell."
End If
End Sub
Bookmarks