I have a multipage form whch i am adding data to a defined range list on page 2, how can i add the data the the dropdown on page 1 without call the initialize routine again?
I have a multipage form whch i am adding data to a defined range list on page 2, how can i add the data the the dropdown on page 1 without call the initialize routine again?
What's the code in the initialise routine that populates the dropdown? You probably need to use the same code again, it's just knowing where. At what stage does the dropdown need refreshing?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Hi p45cal
This is the code in the initialise
On page 2 of the form the user can add job codes to the defined list on sheet "JCodes", when you go back to page 1 on the form the additional codes added do not appear, Unless you close the form & reopen it again or re-initialise, the problem i have found that when you do the latter it also creates the list twice.
Do i need to reset the fileds to " " when 1st initialising ?
[vba]
Private Sub UserForm_Initialize()
Dim lngWinState As XlWindowState
Dim CboSName As Range
Dim CboJCode As Range
Dim CboJCNo As Range
Dim ws As Worksheet
Dim LastRow As Long
With Application
.ScreenUpdating = False
lngWinState = .WindowState
.WindowState = xlMaximized
Me.Move 0, 0, .Width, .Height
.WindowState = lngWinState
.ScreenUpdating = True
End With
Set ws = Worksheets("CData")
'// Get the List for All the customer short name
For Each CboSName In ws.Range("SName")
With Me.CboSName
.AddItem CboSName.Value
.List(.ListCount - 1, 1) = CboSName.Offset(0, 1).Value
End With
Next CboSName
Set ws = Worksheets("JCodes")
'// Get the List for All the Job Codes
For Each CboJCode In ws.Range("JCode")
With Me.CboJCode
.AddItem CboJCode.Value
.List(.ListCount - 1, 1) = CboJCode.Offset(0, 1).Value
End With
Next CboJCode
For Each CboJCNo In ws.Range("JCode")
With Me.CboJCNo
.AddItem CboJCNo.Value
.List(.ListCount - 1, 1) = CboJCNo.Offset(0, 1).Value
End With
Next CboJCNo
'FrmExpress.MultiPage1.Value = 0
End Sub
[/vba]
Rob
Instead of:
[VBA]For Each CboJCode In ws.Range("JCode")
With Me.ComboBox1
.AddItem CboJCode.Value
.List(.ListCount - 1, 1) = CboJCode.Offset(0, 1).Value
End With
Next CboJCode[/VBA]it would be easier to do the likes of:
[VBA]Me.CboJCNo.List = ws.Range("JCode").Resize(, 2).Value[/VBA]
I'm assuming that the named range is dynamic, or adjusts its size as things are added.
Just executing that one line will refresh (and initially populate too) the combobox.
So use it straight after you've added stuff to the sheet from page2 of the multipage.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Hi p45cal
It is a dynamic range for the job codes
I have added your routine to page 1 job code change event and it works a treat, its now refreshing all the list as it should.
Thanks for your help much appreciated
Rob