hi everybody,
i have a code using worksheet_selectionchange to detect if a change has been made within a defined range.
i have a module with the following code to first define the range:
Option Explicit
Public Rprecomm As Range
Private Sub findcell(phrase As String)
Cells.Find(what:=phrase, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub
Sub define_ranges()
Dim top As String
Dim bottom As String
Dim startcell As Range
Dim endcell As Range
'PHASE 1
Range("a1").Select
Call findcell("HEY")
top = "YO"
bottom = "SUP"
Call findcell(top)
Set startcell = ActiveCell.Offset(1, 0)
Call findcell(bottom)
Set endcell = ActiveCell.Offset(-1, 0)
Set Rprecomm = Range(startcell, endcell)
end subnext is the code to detect the if the change occurs with in the defined range. this code is within Sheet(1).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Columns("B:C")) Is Nothing Then Exit Sub
Call define_ranges
If Not Intersect(Target, Rprecomm) Is Nothing Then
MsgBox "hahaha"
End If
End Subwhen i click play by itself, define_ranges work. i tested it by adding code to select Rprecomm and the correct range of cells are selected. when i click outside Columns("B:C"), the code executes correctly, which is to exit.
however, when i click within Columns("B:C"), it goes into debug mode and sub findcell is highlighted. also, when i go back into the excel sheet, i cannot click anything as the cell being selected is stuck at A1.
1)is the error with the declaration of Public Rprecomm or sub findcell? i use sub findcell for my other codes and it works fine.
2) another minor thing is that i want to execute selectionchange only after i input something, not when i click on a cell. how should i modify the code to reflect this?
thanks for the help!