Range - using in code

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hi, All! Excel coding is a whole new ball game!
I set ranges (A5:F11) as "PeopleDrivers" and range (A13:F26) as
"ProcessDrivers".

Column F is a "Click here" column that is suppose to open a form based on
code, and I hope based on the row.

I want to say if the cursor is on column F within the row range (5 through
12), open RiskForm,
if the cursor is on column F within the row range (13 through 26), open
ProcessForm.... And so on.

I don't seem to understand ActiveCell, rows, columns, ranges.... And would
appreciate your guidance.

Cheers,
Stephanie
 
You'll need to use event coding. Right-click on sheet tab, view code. This
one will activate upon cell selection.
'========
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("F5:F12")) Is Not Nothing Then
'What you want to happen under condition 1
Call Macro1
End If
If Intersect(Target, Range("F13:F26")) Is Not Nothing Then
'What you want to happen under condition 2
Call Macro2
End If
End Sub
'========

OR, if you want to make it so that user has to double click (can lead to
less annoyance) change title to:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
 
Right click the Sheet tab and view code. Paste the below code and try...You
can remove the Msgboxs

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, Range("F5:F12")) Is Nothing Then
MsgBox "Riskform"
Load UserForm1
UserForm1.Show
ElseIf Not Application.Intersect(ActiveCell, Range("F13:F26")) Is Nothing Then
MsgBox "Process form"
Load UserForm2
UserForm2.Show
End If
End Sub

If this post helps click Yes
 
Not sure if this posted...

Thanks. I am trying to modify an exisiting module. Let me post it here and
perhaps the group can tell me where I've gone wrong.

**This works well**
Public Sub FormLaunch()
If ActiveSheet.Name = "Risk Criteria" Then
Else
If ActiveCell.Column = 6 And ActiveCell.Row > 4 Then
If Cells(ActiveCell.Row, ActiveCell.Column - 4) = Range("mcdLanguage").Value
Then
RiskFormMCD.Show

**Into the Abyss!**
If Cells(ActiveCell.Row, ActiveCell.Column) = Range("PeopleDrivers").Value
Then
RiskForm.Show
Else
If Cells(ActiveCell.Row, ActiveCell.Column) = Range("ProcessDrivers").Value
Then
ProcessForm.Show

Thanks,
Stephanie
 
Back
Top