urgent please. can anybody explain this?

[michael.beckinsale]

Hi All,

Below is a snippet of code that l have used many times in my current
workbook (with the exception of the first line which l have put in for
testing purposes) to set / rename ranges.

The problem is that l have a dynamic named range which is used to
populate the row source of a combo box on a user form. Hence l need to
call this code each time the form adds a new record.

The code does what it says, deletes existing & creates new. Problem is
that the user form then does not recognise the row source. If l
manually delete the named range and manually recreate the user form
recognises it!

The named range is used on different sheet to where it resides and l
have checked that it has global properties

Any help greatly appreciated

Sub resetrnIL()
Dim ILR
ActiveWorkbook.Names("rnInternal_Locations").Delete
Sheets("Internal Locations").Visible = True
Sheets("Internal Locations").Select
Columns("J").Hidden = False
Range("J2").Select
ILR = Range(Selection, Selection.End(xlDown)).Address
ActiveWorkbook.Names.Add Name:="rnInternal_Locations", _
RefersTo:="=Internal Locations!" & ILR
Columns("J").Hidden = True
End Sub


Regards

Michael beckinsale

 

[Guest]

that isn't what people usually refer to as a dynamic named range. However,
if you are running the code anyway, why not just add a line like this

Sub resetrnIL()
Dim ILR
ActiveWorkbook.Names("rnInternal_Locations").Delete
Sheets("Internal Locations").Visible = True
Sheets("Internal Locations").Select
Columns("J").Hidden = False
Range("J2").Select
ILR = Range(Selection, Selection.End(xlDown)).Address
ActiveWorkbook.Names.Add Name:="rnInternal_Locations", _
RefersTo:="=Internal Locations!" & ILR
Columns("J").Hidden = True
Userform1.Listbox1.RowSource = "rnInternal_Locations"
End Sub

 

[michael.beckinsale]

Tom,

Sorry l was not intending to mislead you but the form writes / deletes
on a database type sheet. The named range is then used on a different
form which picks up data from the entries made on the original form,
hence my description of dynamic.

I populate the combobox on the form initialize event (along with approx
200 more combo / text boxes) but your tip is one l can use in the
future.

I have not done anything to the code but it now runs! however l am
concerned that it may not be robust enough for distribution. Can you
see anything in the code that might be construed as 'flakey'

Your help as always is greatly appreciated

Regards


Michael Beckinsale

 

[Guest]

Sub resetrnIL()
Dim ILR
ActiveWorkbook.Names("rnInternal_Locations").Delete
with Sheets("Internal Locations")
.Range(.Range("J2"),.Range("J2").End(xlDown)).Name = _
"rnInternal_Locations"
End With
Userform1.Listbox1.Rowsource = "rnInternal_Locations"
End Sub

Would avoid unhiding anything.

As long as there will always be entries in at least J2 and J3, this should
work.

 

[michael.beckinsale]

Tom,

Thanks.

Problem appears to be resolved and code runs much quicker.

Regards

Michael Beckinsale