Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using named Excel range in VBA
I have ranges named cat1r, cat2r, cat3r... cat28r on multiple
worksheets inside my workbook. I am trying to format the number style of the ranges based upon the contents of corresponding combo boxes on a "setup" worksheet. sub Type1_click () format_change( "cat1r", type1.value) end sub sub Type2_click () ' ---------------------there are 28 boxes and 28 range names format_change( "cat2r", type2.value) end sub ------ Sub format_change (MyRng as range, MyType as string) dim MyShts MyShts = array (........................ ' load array with names of worksheets if MyType= "Time" For i = 0 to 15 With Sheets(MyShts(i)).Range(MyRng).Validation <SNIP ' ----------- Validate to dropdown using time 0:00 to 8:00 by :15 Else For i = 0 to 15 With Sheets(MySht(i)).Range(CATr1).Validation <SNIP ' -------------------- Validate to 0-999 End If End Sub I keep gettinng a runtime error at the With statement in regards to the range name. please help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using named Excel range in VBA
Adam, you need to convert the text name, "cate1r" to a range. For example:
Range("cat1r") better: Worksheets("worksheetname").Range("cat1r") best Workbooks("workbookname").Worksheets("worksheetnam e").Range("cat1r") Also, call the routine by not including the arguments in parentheses: format_change Range("cat1r"), type1.value Using ()'s converts a range to a value if my memory is working.... Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel wrote in message oups.com... I have ranges named cat1r, cat2r, cat3r... cat28r on multiple worksheets inside my workbook. I am trying to format the number style of the ranges based upon the contents of corresponding combo boxes on a "setup" worksheet. sub Type1_click () format_change( "cat1r", type1.value) end sub sub Type2_click () ' ---------------------there are 28 boxes and 28 range names format_change( "cat2r", type2.value) end sub ------ Sub format_change (MyRng as range, MyType as string) dim MyShts MyShts = array (........................ ' load array with names of worksheets if MyType= "Time" For i = 0 to 15 With Sheets(MyShts(i)).Range(MyRng).Validation <SNIP ' ----------- Validate to dropdown using time 0:00 to 8:00 by :15 Else For i = 0 to 15 With Sheets(MySht(i)).Range(CATr1).Validation <SNIP ' -------------------- Validate to 0-999 End If End Sub I keep gettinng a runtime error at the With statement in regards to the range name. please help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using named Excel range in VBA
Part of my original problem was that the range names were not properly
declared. I worked with it a bit and got it to almost work. except for some reason, when it formats for "Time" the data validation does not operate with the dropdown when the ComboBox value changes. If I go in and run the Sub manually from the VBE interface the dropdown list appears, but not when the ComboBox is changed from the worksheet. I don't understand why... Without the "On Error" statement I get a runtime error that states: "Run-time error '-2147417848(80010108)': Automation error The object invoked has disconnected from its clients" **The long list of WorkSheet names, and the comma seperated time list, are removed from the Subs/Functions below for visual formatting Private Sub Type1_Change() '------there are 28 subs like this one for ComboBoxes If Type1.Text = "Time" Or Type1.Text = "Qty" Or Type1.Text = "N/A" Then Call CatFormat("CATr1", Type1.Text) End If End Sub ================================================== ============ Function CatFormat(MyCat As String, CatVal As String) Dim MySheets, i As Integer MySheets = Array ( ***list of 16 worksheet names***) If CatVal = "Time" Then For i = 0 To 15 With Sheets(MySheets(i)).Range(MyCat).Validation .Delete On Error Resume Next .Add xlValidateList, xlValidAlertStop, xlBetween, " ***0 through 8:00 in 0:15 incriments*** .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Sheets(MySheets(i)).Range(MyCat).NumberFormat = "[h]:mm" Next i Else For i = 0 To 15 With Sheets(MySheets(i)).Range(MyCat).Validation .Delete On Error Resume Next .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="0", Formula2:="999" .IgnoreBlank = True .InCellDropdown = False .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Sheets(MySheets(i)).Range(MyCat).NumberFormat = "0" Next i End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Excel will not include all of my non-adj ranges in a named range?? | Excel Worksheet Functions | |||
Passing Excel NAMED Range to VBA | Excel Worksheet Functions | |||
Paste a named range to another range in Excel | Excel Discussion (Misc queries) |