Haydan
asked on
Have form Dropdown ComboBox always start at first option
I have a forms combo box on each one of my workbooks pages that lists all the current pages in the workbook and when the client selects a page the workbook jumps to that page.
What I would like is for the list, when opened, to always start at the top of the list, and not at the entry of the last sheet selected (the list is large and doesn't all hit on screen).
Is there a way to do this?
What I would like is for the list, when opened, to always start at the top of the list, and not at the entry of the last sheet selected (the list is large and doesn't all hit on screen).
Is there a way to do this?
ASKER
The dropdowns are form dropdowns with various names (SheetName(name.name)PageJ umper. Can I add this somehow to the macro that the form's call on?
Public Sub SheetLister()
'===================
'Lists Active Sheets
'===================
Dim sht As Worksheet, i As Integer
Dim TheList As Range
Dim ReturnTo As Worksheet
Set ReturnTo = ActiveSheet
Set TheList = UllB4.Range("V1:V50")
UllB4.Activate
CodeUnlock
TheList.Cells.ClearContent s
i = 1
For Each sht In ActiveWorkbook.Sheets
If sht.Visible = True Then
TheList.Cells(i, 1) = sht.Name
i = i + 1
End If
Next
CodeLock
ReturnTo.Activate
End Sub
Public Sub PageJumper()
'==============
'Page Jump Code
'==============
SheetLister
Dim sName As String
sName = UllB4.Range("$V$52").Value
Sheets(sName).Activate
??????.ListIndex = 0
End Sub
Public Sub SheetLister()
'===================
'Lists Active Sheets
'===================
Dim sht As Worksheet, i As Integer
Dim TheList As Range
Dim ReturnTo As Worksheet
Set ReturnTo = ActiveSheet
Set TheList = UllB4.Range("V1:V50")
UllB4.Activate
CodeUnlock
TheList.Cells.ClearContent
i = 1
For Each sht In ActiveWorkbook.Sheets
If sht.Visible = True Then
TheList.Cells(i, 1) = sht.Name
i = i + 1
End If
Next
CodeLock
ReturnTo.Activate
End Sub
Public Sub PageJumper()
'==============
'Page Jump Code
'==============
SheetLister
Dim sName As String
sName = UllB4.Range("$V$52").Value
Sheets(sName).Activate
??????.ListIndex = 0
End Sub
I am now confused with form dropdowns :)
Are these activex controls or datavalidation boxes?
A sample file will help in a faster resolution....
Sid
Are these activex controls or datavalidation boxes?
A sample file will help in a faster resolution....
Sid
ASKER
This isn't the best of example as there are not many pages in it but I think you'll get the idea from it:
Dropdown lists are on page 1-4 (not verification page.
Macro it referes to are under modules in MiscSubPub
Verification-Tool-Example.xls
Dropdown lists are on page 1-4 (not verification page.
Macro it referes to are under modules in MiscSubPub
Verification-Tool-Example.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A brilliant and so easy thanks
You are welcome :)
Sid
Sid
Do remember to close this question if your query is solved :)
It saves time for members like me who love answering queries ;)
Sid
It saves time for members like me who love answering queries ;)
Sid
ASKER
I will in a sec, just like to check its gonna do what I want it to first (have closed a couple Q's only to find that something aint right a few minutes later)
Oh ok... Sure :)
Do you know the logic behind setting the value of "H68" to 1?
Sid
Do you know the logic behind setting the value of "H68" to 1?
Sid
ASKER
Yep, the list opens to the current value of its linked cell. Dunno why I didn't think to just set it back after it had made the page change - I think its this summer weather and all the early morning fishing missions taking its toll on me. Ah well time to call it a day and go have a few brewskys and a barbie and have an early night ready to head out again in the monring :)
Yep, the list opens to the current value of its linked cell.
Yes you are right :)
Sid
Open in new window
Sid