Userforms and ComboBox

A

Abgar

Active Member
Joined
Jun 20, 2009
Messages
265
Hi guys,
Just chasing a bit of assistance please with a User Form and Combobox(s), as i've never used these before.
I've got the below code which works for the purposes i need:
Code: 
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("CurrentWork")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.cboLoc.Value) = "" Then
  Me.cboLoc.SetFocus
  MsgBox "Please enter your Location"
  Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.cboLoc.Value
ws.Cells(iRow, 2).Value = Me.cboOCA.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value
ws.Cells(iRow, 4).Value = Me.txtOwner.Value
ws.Cells(iRow, 5).Value = Me.txtDate.Value
'clear the data
Me.cboLoc.Value = ""
Me.cboOCA.Value = ""
Me.txtName.Value = ""
Me.txtOwner.Value = ""
Me.txtDate.Value = ""
Me.cboLoc.SetFocus
End Sub
Private Sub cmdClose_Click()
  Unload Me
End Sub
 
Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cPart In ws.Range("Location")
  With Me.cboLoc
    .AddItem cPart.Value
    .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
  End With
Next cPart
For Each cLoc In ws.Range("OCA")
  With Me.cboOCA
    .AddItem cLoc.Value
  End With
Next cLoc
Me.txtDate.Value = Format(Date, "Medium Date")
Me.cboLoc.SetFocus
End Sub

Now the User Form has 2 combo boxes in it (named 'cboLoc' and 'cboOCA').
What i would like is to have the options that display in cboOCA to be dependant on the result from cobLoc.
The comboboxes are pulled from a list on a separate worksheet, with Column A containing 3 locations (for cboLoc) and Column B containing a list of names - i.e. as per below:
LookupLists

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 76px"><COL style="WIDTH: 130px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Location</TD><TD>OCA</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Sydney</TD><TD>Name1</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Sydney</TD><TD>Name2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Sydney</TD><TD>Name3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Sydney</TD><TD>Name4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Sydney</TD><TD>Name5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Sydney</TD><TD>Name6</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Sydney</TD><TD>Name7</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Melbourne</TD><TD>Name8</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Melbourne</TD><TD>Name9</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Melbourne</TD><TD>Name10</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Melbourne</TD><TD>Name11</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Melbourne</TD><TD>Name12</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>Brisbane</TD><TD>Name13</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>Brisbane</TD><TD>Name14</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Brisbane</TD><TD>Name15</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Brisbane</TD><TD>Name16</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Brisbane</TD><TD>Name17</TD></TR></TBODY></TABLE>

Now what i would like is that the second combobox (cboOCA) can not be used until a cboLoc is entered, and once it is entered, display the options in cboOCA as to only display applicable options.
i.e. - if cboLoc is selected as Sydney, then cboOCA should only display the options of Name1 to Name7.

Can anyone help me out with this request? I read something about a Listbox, but i have no idea what that is or what to do with it or where to enter VBA code for it in my above code.

Any help you could provide would be greatly appreciated :)

Thanks so much :)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
As i found in the internet

first transpose your data to be like this

<TABLE style="WIDTH: 182pt; BORDER-COLLAPSE: collapse" dir=rtl border=0 cellSpacing=0 cellPadding=0 width=242><COLGROUP><COL style="WIDTH: 54pt" span=3 width=72><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 832" width=26><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr id=td_post_2700305 class=xl64 height=19 width=72>C</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 54pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=72>B</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 54pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=72>A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr width=26></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 25.5pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl63 height=34 width=72 align=left>Sydney</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl63 width=72 align=left>Melbourne</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl63 width=72 align=left>Brisbane</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>1</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 height=19 width=72 align=left>Name1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name8</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name13</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>2</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 height=19 width=72 align=left>Name2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name9</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name14</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>3</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 height=19 width=72 align=left>Name3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name10</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name15</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>4</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 height=19 width=72 align=left>Name4</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name11</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name16</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 height=20 width=72 align=left>Name5</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72 align=left>Name12</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl66 width=72 align=left>Name17</TD>




<TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>7</TD>
</TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 height=19 width=72 align=left>Name6</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>8</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; HEIGHT: 14.25pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 height=19 width=72 align=left>Name7</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: white; WIDTH: 54pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" dir=ltr class=xl65 width=72></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 20pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" dir=ltr class=xl64 width=26>9</TD></TR></TBODY></TABLE>

then you name your ranges here
go to Define name and NEW
Sydney
=OFFSET(sheet1!$C$2;0;0;COUNTA(sheet1!$C$2:$C$20);1)
Melbourne
=OFFSET(sheet1!$B$2;0;0;COUNTA(sheet1!$B$2:$B$20);1)
Brisbane
=OFFSET(sheet1!$A$2;0;0;COUNTA(sheet1!$A$2:$A$20);1)

then go to your form and put this code
Code: 
Private Sub UserForm_Initialize()
CboLOC.AddItem "Sydney"
CboLOC.AddItem "Melbourne"
CboLOC.AddItem "Brisbane"
End Sub

then go to CboLOC and put this code

Code: 
Private Sub CboLOC_Change()
Dim strRange As String
    If CBX3.ListIndex > -1 Then
       strRange = CboLOC
       strRange = Replace(strRange, " ", "_")
            With CboOCA
                .RowSource = vbNullString
                .RowSource = strRange
                .ListIndex = 0
            End With
    End If
End Sub
and you are done
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
613
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
658
Micron
M
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
518
esben
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,503
Messages
6,179,136
Members
452,890
Latest member
Nikhil Ramesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top