Pass variables to Worksheet_SelectionChange
 

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?

--
Ian
--

Pass variables to Worksheet_SelectionChange
 

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

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

Activesheet.unprotect


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158380

Microsoft Office Help

Pass variables to Worksheet_SelectionChange
 

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.

--
Ian
--

"joel" wrote in message
...

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

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

Activesheet.unprotect


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=158380

Microsoft Office Help