| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| looping and stopping when a condition is met - test | Excel Discussion (Misc queries) | |||
| How do I test a condition in one cell and write to another. | Excel Worksheet Functions | |||
| logical test for an #N/A condition in a cell | Excel Discussion (Misc queries) | |||
| Test condition never satisfied in loop | Excel Programming | |||
| How to end when test condition met? | Excel Programming | |||
Linear Mode
