About ComboBox ; Sheet.Name and commands

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
Hi

I try make UserForm and need help
1)
I have 2 combobox in form and 1command buton.
in combobox1 i put list of items in active sheet selected range.
But i need put list of worksheets in combobox2
i have 1 code but cant finish this
Code:
For r = 1 To Worksheets.Count
'how i can put this answer directly in combo not in some sheet??
Worksheets("Sheet1").Cells(r, 1).Value = Worksheets(r).Name
Next

2)
then how i can put this 2 answer together in this code
Code:
Private Sub CommandButton1_Click()
With ActiveSheet.Range("A1:L500")'<----- in place of Activesheet need be value of combobox2( Sheet choice)
Set c = .Find(what:="here")'<------- in place "here" i need put value of combobox1( find same value and activate )
If Not c Is Nothing Then
c.Activate
Else
MsgBox "Not in this sheet"
End If
End With
End Sub
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
This will add each worksheet name in a workbook to the combobox.

Dim ws as worksheet
For Each ws in activeworkbook.worksheets
Combobox1.additem ws.name
Next

To reference the combobox selected values.
Selection1 as string, selection2 as string

Selection1 = Combox1.value
Selection2 = combox2.value

Hope this helps.
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
first code working for me TY, but second... i think i do something wrong?

Code:
Private Sub CommandButton1_Click()
Dim leid As String, ws As String
leid = ComboBox1.Value
ws = ComboBox2.Value

'error: invalid qualifier, ws
With ws.Range("A1:L500") 
Set new = .Find(what:="leid")
If Not new Is Nothing Then
new.Activate
Else
MsgBox "Not in this sheet"
End If
End With

End Sub
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
backup69 said:
first code working for me TY, but second... i think i do something wrong?

Code:
Private Sub CommandButton1_Click()
Dim leid As String, ws As String
leid = ComboBox1.Value
ws = ComboBox2.Value

'error: invalid qualifier, ws
With ws.Range("A1:L500") 
Set new = .Find(what:="leid")
If Not new Is Nothing Then
new.Activate
Else
MsgBox "Not in this sheet"
End If
End With

End Sub

Dim leid As String, ws As String

Should be leid as string, ws as worksheet

Also

ws = ComboBox2.Value
Should be
Set Ws = Worksheet(combobo2.value)
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
ohh sry but another place now :)

.Find(what:="leid")
not this way
.Find(what:=leid)
not this way
.Find(what:=(leid))
and not this way
 

backup69

Active Member
Joined
Jan 20, 2004
Messages
271
i found some solution

Dim leid As String, ws As Worksheet
leid = ComboBox1.Text '<-------- only this way vorks, whay i dont cnow :)
Set ws = Worksheets(ComboBox2.Value)


With ws.Range("A1:L500")
Set uus = .Find(what:=leid)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,671
Messages
5,549,344
Members
410,910
Latest member
DessertDiva
Top