PDA

View Full Version : Solved: Multi page form refreshing data



Rob342
04-20-2012, 10:09 AM
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?

p45cal
04-22-2012, 01:15 AM
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?

Rob342
04-22-2012, 12:46 PM
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 ?

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



Rob

p45cal
04-22-2012, 01:35 PM
Instead of:
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 CboJCodeit would be easier to do the likes of:
Me.CboJCNo.List = ws.Range("JCode").Resize(, 2).Value
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.

Rob342
04-23-2012, 01:00 AM
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