help calling up another sub when targets in a certain range ( can someone let me know if this is pos
Posted by steve on March 10, 2001 10:12 AM
Can someone help me,
I'm trying to get one sub to call up another sub whenever the target cell is in a certain range(a & rw : d & rw). I then want to set a watch range and to be able to target another cell in a different range but same row(e & rw : h & rw).
Can someone let me know if this is posible.
Thanks for eany help you can provide
steve
Posted by Dave Hawley on March 10, 2001 3:01 PM
Hi steve
here is an example: In a normal module paste this:
Public Watchrange As Range, Rw As Long
Sub MyMacro()
MsgBox Watchrange.Address
End Sub
Now in the Sheet Module paste this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
Rw = Target.Row
If Not Intersect _
(Target, Range("A" & Rw & ":" & "D" & Rw)) Is Nothing Then
Set Watchrange = Range("E" & Rw & ":" & "H" & Rw)
Run "MyMacro"
End If
End Sub
change the value in any cell in Columns A:D and you will see the address of the new watchrange.
If the sub you are wanting to run also resides in the Private Sheet Module then omit the "Run" and use simply: MyMacro
Dave
Dave
OzGrid Business Applications
Posted by steve on March 10, 2001 3:42 PM
Now in the Sheet Module paste this:
change the value in any cell in Columns A:D and you will see the address of the new watchrange. If the sub you are wanting to run also resides in the Private Sheet Module then omit the "Run" and use simply: MyMacro
Dave Dave
dave
works good i changed change to selection change, but i need to highlight the cell also before it changes the watch range.
thanks again
steve
Posted by steve on March 10, 2001 4:07 PM
Forget about calling up another macro I just need to highlight the cell selected in both ranges
dave
all I need is to highlight the first cell in the range set another range and then for it to highlight the cell selected in the second range.
I'm not sure would this be accomplished easier with multiple macros. This is why I was asking about calling another macro.
Thanks for the help
steve
Posted by Dave Hawley on March 10, 2001 4:21 PM
Re: Forget about calling up another macro I just need to highlight the cell selected in both ranges
Steve, try this then.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim Rw As Long
If Target.Cells.Count > 1 Then Exit Sub
Rw = Target.Row
If Not Intersect _
(Target, Range("A" & Rw & ":" & "D" & Rw)) Is Nothing Then
Set WatchRange = Range("E" & Rw & ":" & "H" & Rw)
Target.Interior.ColorIndex = 5
WatchRange.Cells(1, 1).Interior.ColorIndex = 5
End If
End Sub
OzGrid Business Applications
Posted by steve on March 10, 2001 4:32 PM
Its not letting me select a cell in second range
Dave
it highlights the first cell fine, but it automatically highlights the first cell in the second range, I was wanting to be able to select a cell in the second range.
thanks
steve
Posted by Dave Hawley on March 10, 2001 4:39 PM
Re: Its not letting me select a cell in second range
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim Rw As Long
If Target.Cells.Count > 1 Then Exit Sub
Rw = Target.Row
If Not Intersect _
(Target, Range("A" & Rw & ":" & "D" & Rw)) Is Nothing Then
Application.EnableEvents = False
Set WatchRange = Range("E" & Rw & ":" & "H" & Rw)
Target.Interior.ColorIndex = 5
WatchRange.Cells(1, 1).Select
Application.EnableEvents = True
End If
End Sub
Dave
OzGrid Business Applications
Posted by steve on March 10, 2001 5:16 PM
Re:all it does is select the first cell in the second range
Dave
Dave all it does is select the first cell in the second range., I'm not sure but I'm thinking that it may need a second macro to allow to select a cell in the second range. What do you think.
Thanks steve
Posted by Dave Hawley on March 10, 2001 5:28 PM
Re:all it does is select the first cell in the second range
>allow to select a cell in the second range
Steve, which cell do you want to select ?
Dave
OzGrid Business Applications
Posted by steve on March 10, 2001 6:38 PM
Re:all it does is select the first cell in the second range
>allow to select a cell in the second range Steve, which cell do you want to select ? Dave
dave
Lets E1 threw H1 was selected, I now want to be able to select cell I1 or J1 or K1 or L1. I'll let you know kind off what I'm doing and maybe you might have an idea. The user of this program will first select what product they want, then they will select what style of frame they want to go along with it. The products are in columns E-H And the frame styles that corrispond to them are in columns I-L, there are also products in M-P and corrisponding frames in Q-T. What I'm trying to is make it so the user cann't pick a frame that doesn't corrispond to the product.
The highlighting is to let the user know what they selected. The thing that is being highlighted is the price of the product and then it is transferred to another cell.
Thanks steve
Here's my current code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
Dim Cl As Long
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("E4:H31,M4:P31")
If Not Intersect(Target, WatchRange) Is Nothing Then
If Target = "N/A" Then Exit Sub
With Target
Rw = .Row
Cl = .Column
If Target.Column = Range("X" & Rw) Then
With Target
Range("E" & Rw & ":" & "H" & Rw & "," & "M" & Rw & ":" & "P" & Rw). _
Font.ColorIndex = ColorIndexNone
.Font.ColorIndex = 1
End With
Else
With Target
Range("E" & Rw & ":" & "H" & Rw & "," & "M" & Rw & ":" & "P" & Rw). _
Font.ColorIndex = ColorIndexNone
.Font.ColorIndex = 3
End With
End If
End With
If Target.Column = Range("X" & Rw) Then
With Range("X" & Rw)
Range("W" & Rw) = ""
Range("X" & Rw) = ""
End With
'End If
Else
With Range("X" & Rw)
Range("X" & Rw) = Cl
Range("W" & Rw) = Target
End With
End If
Set WatchRange = Nothing
End If
End Sub