Test for condition before running macro
 

I have 10 named non consecutive ranges Scl1 - Scl10. I am going to create a
Command bar to run a macro based on the current ActiveCell (Selection = Some
value - from a user form). I need to understand how to test that the selected
cell is indeed named Scl* or Scl# (a wildcard will work I think).

If ActiveCell.range = "scl*" then
UserForm.Show


I also need a method for the UserForm to paste tthe data in the selected cell.

Private Sub OptionButton1_Click() 'Private Sub
Range(ActiveCell) = OurMagic

I've tried everyway I can for the test function and have even less hair than
when I started.
TIA
Lou

Test for condition before running macro
 

I'm running Excel 2003.
Thanks again
Lou

"Rookie 1st class" wrote:

I have 10 named non consecutive ranges Scl1 - Scl10. I am going to create a
Command bar to run a macro based on the current ActiveCell (Selection = Some
value - from a user form). I need to understand how to test that the selected
cell is indeed named Scl* or Scl# (a wildcard will work I think).

If ActiveCell.range = "scl*" then
UserForm.Show


I also need a method for the UserForm to paste tthe data in the selected cell.

Private Sub OptionButton1_Click() 'Private Sub
Range(ActiveCell) = OurMagic

I've tried everyway I can for the test function and have even less hair than
when I started.
TIA
Lou

Test for condition before running macro
 

Will this work?

Sub testRanges()

NamedRanges = Array("FirstRange", "SecondRange", "ThirdRange")

For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
'enter your code here
Exit For
End If

Next NRange

End Sub

"Rookie 1st class" wrote:

I'm running Excel 2003.
Thanks again
Lou

"Rookie 1st class" wrote:

I have 10 named non consecutive ranges Scl1 - Scl10. I am going to create a
Command bar to run a macro based on the current ActiveCell (Selection = Some
value - from a user form). I need to understand how to test that the selected
cell is indeed named Scl* or Scl# (a wildcard will work I think).

If ActiveCell.range = "scl*" then
UserForm.Show


I also need a method for the UserForm to paste tthe data in the selected cell.

Private Sub OptionButton1_Click() 'Private Sub
Range(ActiveCell) = OurMagic

I've tried everyway I can for the test function and have even less hair than
when I started.
TIA
Lou

Test for condition before running macro
 

You are a good man Joel. It works as I hoped.
Part 2 isnt as easy as I'd hoped. How do offset where the UserForm places
its data? I thought I could just use ActiveCellOffset(0,3) = "Our magic". Is
there an easy way to make the UserForm reply dynamic too?
Thanks
Lou

"joel" wrote:

Will this work?

Sub testRanges()

NamedRanges = Array("FirstRange", "SecondRange", "ThirdRange")

For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
'enter your code here
Exit For
End If

Next NRange

End Sub

"Rookie 1st class" wrote:

I'm running Excel 2003.
Thanks again
Lou

"Rookie 1st class" wrote:

I have 10 named non consecutive ranges Scl1 - Scl10. I am going to create a
Command bar to run a macro based on the current ActiveCell (Selection = Some
value - from a user form). I need to understand how to test that the selected
cell is indeed named Scl* or Scl# (a wildcard will work I think).

If ActiveCell.range = "scl*" then
UserForm.Show


I also need a method for the UserForm to paste tthe data in the selected cell.

Private Sub OptionButton1_Click() 'Private Sub
Range(ActiveCell) = OurMagic

I've tried everyway I can for the test function and have even less hair than
when I started.
TIA
Lou

Test for condition before running macro
 

You have to be smart in the way you design a userform so it makes it easy to
read and write the data. You can use Activecell.Offset(X,Y) and the values
of X and Y can be values from a FOR loop. Depending on your organization of
your data will be the determining factor on how complicated the macro will be.

"Rookie 1st class" wrote:

You are a good man Joel. It works as I hoped.
Part 2 isnt as easy as I'd hoped. How do offset where the UserForm places
its data? I thought I could just use ActiveCellOffset(0,3) = "Our magic". Is
there an easy way to make the UserForm reply dynamic too?
Thanks
Lou

"joel" wrote:

Will this work?

Sub testRanges()

NamedRanges = Array("FirstRange", "SecondRange", "ThirdRange")

For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
'enter your code here
Exit For
End If

Next NRange

End Sub

"Rookie 1st class" wrote:

I'm running Excel 2003.
Thanks again
Lou

"Rookie 1st class" wrote:

I have 10 named non consecutive ranges Scl1 - Scl10. I am going to create a
Command bar to run a macro based on the current ActiveCell (Selection = Some
value - from a user form). I need to understand how to test that the selected
cell is indeed named Scl* or Scl# (a wildcard will work I think).

If ActiveCell.range = "scl*" then
UserForm.Show


I also need a method for the UserForm to paste tthe data in the selected cell.

Private Sub OptionButton1_Click() 'Private Sub
Range(ActiveCell) = OurMagic

I've tried everyway I can for the test function and have even less hair than
when I started.
TIA
Lou

Test for condition before running macro
 

Sometimes I scare myself. Part 2 was as simple as I'd hoped. Thank You for
your help.
Lou

"joel" wrote:

You have to be smart in the way you design a userform so it makes it easy to
read and write the data. You can use Activecell.Offset(X,Y) and the values
of X and Y can be values from a FOR loop. Depending on your organization of
your data will be the determining factor on how complicated the macro will be.

"Rookie 1st class" wrote:

You are a good man Joel. It works as I hoped.
Part 2 isnt as easy as I'd hoped. How do offset where the UserForm places
its data? I thought I could just use ActiveCellOffset(0,3) = "Our magic". Is
there an easy way to make the UserForm reply dynamic too?
Thanks
Lou

"joel" wrote:

Will this work?

Sub testRanges()

NamedRanges = Array("FirstRange", "SecondRange", "ThirdRange")

For Each NRange In NamedRanges
Set isect = Application.Intersect(Range(NRange), ActiveCell)
If Not isect Is Nothing Then
'enter your code here
Exit For
End If

Next NRange

End Sub

"Rookie 1st class" wrote:

I'm running Excel 2003.
Thanks again
Lou

"Rookie 1st class" wrote:

I have 10 named non consecutive ranges Scl1 - Scl10. I am going to create a
Command bar to run a macro based on the current ActiveCell (Selection = Some
value - from a user form). I need to understand how to test that the selected
cell is indeed named Scl* or Scl# (a wildcard will work I think).

If ActiveCell.range = "scl*" then
UserForm.Show


I also need a method for the UserForm to paste tthe data in the selected cell.

Private Sub OptionButton1_Click() 'Private Sub
Range(ActiveCell) = OurMagic

I've tried everyway I can for the test function and have even less hair than
when I started.
TIA
Lou