Using named Excel range in VBA

[adam_kroger]

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

 

[Bob Flanagan]

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("worksheetname").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

 

[Dave Peterson]

Without looking too close, it looks like you're passing a string to that
Format_Change function.

What happens if you try:
Sub format_change (MyRng as String, MyType as string)

I'm also confused about
With Sheets(MySht(i)).Range(CATr1).Validation

I see a string catlr, but no variable.

====
If myrng is a range, then
range(myrng) only makes sense if myrng is a single cell and holds something like
an address:
range("A1") (range(myrng.value))

And every range already has its own parent. So using:
worksheets("somesheet").myrng
won't work.

You could use something like this (depending on what you're doing):
worksheets("somesheet").range(myrng.address)
or
worksheets("somesheet").range(myrng.value)

 

[adam_kroger]

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