Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
Public Sub TestRanges() 'Sort or Modify columns Macro
Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For End If Next NRange End Sub Thanks to Joel for this code. It works great provided I select one of the "Scls". I'd like to add a message that tells the user what to do if one of the Scls isn't selected. TIA Lou |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
I'm getting old... Excel 2003
"Rookie 1st class" wrote: Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For End If Next NRange End Sub Thanks to Joel for this code. It works great provided I select one of the "Scls". I'd like to add a message that tells the user what to do if one of the Scls isn't selected. TIA Lou |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
Maybe add an Else to your IF like this: Public Sub TestRanges() 'Sort or Modify columns Macro Dim mBox As Variant Dim Nxt As Variant Nxt: Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For Else mBox = MsgBox("You have not selected a range", vbYesNo, "Range not detected") If mBox = vbYes Then GoTo Nxt End If Next NRange End Sub Rookie 1st class;289973 Wrote: Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For End If Next NRange End Sub Thanks to Joel for this code. It works great provided I select one of the "Scls". I'd like to add a message that tells the user what to do if one of the Scls isn't selected. TIA Lou -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81040 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
For what you posted, try this...
Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", _ "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit Sub End If Next NRange MsgBox "Nothing was selected!" End Sub However, given your Remark in the code, I'm guessing this is not all of the macro's code, so perhaps you need this instead... Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", _ "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll GoTo Continue End If Next NRange MsgBox "Nothing was selected!" Continue: ' ' << Rest of your code goes here ' End Sub -- Rick (MVP - Excel) "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For End If Next NRange End Sub Thanks to Joel for this code. It works great provided I select one of the "Scls". I'd like to add a message that tells the user what to do if one of the Scls isn't selected. TIA Lou |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
Thanks guys.
I had a basic knowledge of what to do just not certain of where. Final code below. Lou Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For End If Next NRange MsgBox "Select one of the ""Scale under test"" blocks." End Sub "Simon Lloyd" wrote: Maybe add an Else to your IF like this: Public Sub TestRanges() 'Sort or Modify columns Macro Dim mBox As Variant Dim Nxt As Variant Nxt: Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For Else mBox = MsgBox("You have not selected a range", vbYesNo, "Range not detected") If mBox = vbYes Then GoTo Nxt End If Next NRange End Sub Rookie 1st class;289973 Wrote: Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For End If Next NRange End Sub Thanks to Joel for this code. It works great provided I select one of the "Scls". I'd like to add a message that tells the user what to do if one of the Scls isn't selected. TIA Lou -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=81040 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
Final - Final
It flashed the MsgBox even if one of the Arrays was selected. An additional Exit For corrected that. "Flashes HUGE Grin" Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For Else MsgBox "Select one of the ""Scale under test"" blocks, then run again." Exit For End If Next NRange End Sub I owe Joel, Simon, and Rick a beverage of their choice Many Thanks Guys Lou |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
You didn't copy my code as I posted it (you should always consider
copy/pasting rather than re-typing)... I didn't have an Exit For... I had an Exit Sub in the If..Then block. I'm pretty sure the code I posted would have worked as written. In any event, you have working code now, which is all that matters. I'll be sure to have an online cup of coffee on you... thanks.<g -- Rick (MVP - Excel) "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Final - Final It flashed the MsgBox even if one of the Arrays was selected. An additional Exit For corrected that. "Flashes HUGE Grin" Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For Else MsgBox "Select one of the ""Scale under test"" blocks, then run again." Exit For End If Next NRange End Sub I owe Joel, Simon, and Rick a beverage of their choice Many Thanks Guys Lou |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add a message to this array.
Bows to Rick. Your code worked properly.
I owe you 2 beverages of your choice. Thanks again Lou "Rick Rothstein" wrote: You didn't copy my code as I posted it (you should always consider copy/pasting rather than re-typing)... I didn't have an Exit For... I had an Exit Sub in the If..Then block. I'm pretty sure the code I posted would have worked as written. In any event, you have working code now, which is all that matters. I'll be sure to have an online cup of coffee on you... thanks.<g -- Rick (MVP - Excel) "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Final - Final It flashed the MsgBox even if one of the Arrays was selected. An additional Exit For corrected that. "Flashes HUGE Grin" Public Sub TestRanges() 'Sort or Modify columns Macro Sheets("Rockwell").Select NamedRanges = Array("Scl1", "Scl2", "Scl3", "Scl4", "Scl5", "Scl6", "Scl7", "Scl8", "Scl9", "Scl10") For Each NRange In NamedRanges Set isect = Application.Intersect(Range(NRange), ActiveCell) If Not isect Is Nothing Then Worksheets("Rockwell").Select Load Rckwll Rckwll.Show Unload Rckwll Exit For Else MsgBox "Select one of the ""Scale under test"" blocks, then run again." Exit For End If Next NRange End Sub I owe Joel, Simon, and Rick a beverage of their choice Many Thanks Guys Lou |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing contents of an array into a message box | Excel Programming | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
replace VBA run-time error message with custom message | Excel Programming | |||
Intercept/replace standard 'cell protected' message with my own message? | Excel Programming | |||
Displaying a message in a message box without requiring user to click anything to proceed | Excel Programming |