Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent please. can anybody explain this?
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").Delet e 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent please. can anybody explain this?
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").Delet e 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 -- Regards, Tom Ogilvy "michael.beckinsale" wrote: 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").Delet e 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent please. can anybody explain this?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent please. can anybody explain this?
Sub resetrnIL()
Dim ILR ActiveWorkbook.Names("rnInternal_Locations").Delet e 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. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "michael.beckinsale" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent please. can anybody explain this?
Tom, Thanks. Problem appears to be resolved and code runs much quicker. Regards Michael Beckinsale |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
Not sure how to explain.... | Excel Discussion (Misc queries) | |||
Explain these please | Excel Worksheet Functions | |||
Can someone explain to me? Pls? | Excel Programming | |||
Can someone explain to me? Pls? | Excel Programming |