Pass variables to Worksheet_SelectionChange

[IanC]

I have the following code which works well as it stands. My problem is that
I need to vary the range depending on conditions set in other code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----- ENABLE TICKS IN RELEVANT BOXES -----
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Worksheets("Room").Range("P11:T39")) Is Nothing
Then
With Target
If .Value = Chr(252) Then
.Value = ""
Else
.Value = Chr(252)
.Font.Name = "Wingdings"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub


I've tried changing the first line to Private Sub
Worksheet_SelectionChange(ByVal Target As Range, r, c) to pass row and
column but this causes a problem earlier in my code with an apparently
unrelated instruction.

To explain further, I have a form which the enables the user to select from
a number of lists using comboboxes. Once the comboboxes are populated, the
user presses a commandbutton.

Private Sub CommandButton1_Click()
With Worksheets("Room")
.Unprotect
.Range("F3") = ComboBox1.Text
.Range("W3") = ComboBox2.Text
.Range("I2") = CDate(TextBox1.Value)
.Visible = True
.Activate
.Range("A1").Select
.Protect
End With
Worksheets("Lookup").Visible = False
UserForm1.Hide
Call Worksheets("Room").UserForm_Reaction
With ActiveWindow
.WindowState = xlMaximized
End With
Worksheets("Room").Range("F2").Select
End Sub

The line causing the problem is .Unprotect but the code runs perfectly with
the original Worksheet_SelectionChange line.

Perhaps I'm expecting the impossible in trying to make the target range
dynamic.

Any ideas?

 

[joel]

A worksheet change will only acccept Target as a parameter. You ca
either use a cell in the worksheet as a parameter to the function or
named range.

To get the uprotect to work you need to specify the activesheet

Activesheet.unprotec

 

[John Bundy]

You are telling the sheet to unprotect but not providing a password, if there
is one you will have to provide it. I used your code and it worked without
issue.

 

[IanC]

Thanks Joel. The named range option looks promising, though I only need to
the code apply to part of a named range (ignoring the top 4 rows and the
left column). I may need to introduce another named range.

 

[IanC]

There is no password on the worksheet and, as you say, the code works as it
stands. It's only when I tried to introduce a variable into the worksheet
selection change routine that the unprotect issue appeared. I now know that
this method isn't possible, so the unprotect issue no longer exists.