asked on

New Form

I have created an Excel form in VBA. I have two radio buttons at the top of the form. One Warehouse and one Non-Warehouse. In the body of the form I have two ComboBoxes.

If the Radio Button at the top of the form is selcted for "Warehouse", I would like to hide the combobox for Site ID. If the radio button at the top of the form is selected for "Non-Warehouse", I would like to hide the the combobox for "Division"

The Warehouse Radio Button is named ObjectButton1
The Non-Warehouse Radio Button is named ObjectButton2

The combobox associated with the the Division is named ComboBox3
The combobox associated with the SiteID is named ComboBox7

Here is the current code
Private Sub cmdAdd_Click()

Dim lRow As Variant
Dim lPart As Variant
Dim ws As Worksheet
Set ws = Worksheets("Prorizon Purchasing Transmittal")

'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws

.Cells(lRow, 1).Value = Me.TextBox1
.Cells(lRow, 2).Value = Me.TextBox2
.Cells(lRow, 3).Value = Me.TextBox3
.Cells(lRow, 4).Value = Me.ComboBox1
.Cells(lRow, 5).Value = Me.TextBox4
.Cells(lRow, 6).Value = Me.ComboBox2
.Cells(lRow, 7).Value = Me.ComboBox3
.Cells(lRow, 8).Value = Me.TextBox5
.Cells(lRow, 9).Value = Me.ComboBox4
.Cells(lRow, 10).Value = Me.TextBox6
.Cells(lRow, 11).Value = Me.ComboBox5
.Cells(lRow, 12).Value = Me.TextBox7
.Cells(lRow, 13).Value = Me.TextBox8
.Cells(lRow, 14).Value = Me.TextBox9
.Cells(lRow, 15).Value = Me.TextBox10
.Cells(lRow, 16).Value = Me.TextBox11
.Cells(lRow, 17).Value = Me.TextBox12
.Cells(lRow, 18).Value = Me.ComboBox6

End With

'clear the data
Me.TextBox1 = ""
Me.TextBox2 = ""
Me.TextBox3 = ""
Me.ComboBox1 = "Select"
Me.TextBox4 = ""
Me.ComboBox2 = "Select"
Me.ComboBox3 = "Select"
Me.TextBox5 = ""
Me.ComboBox4 = "Select"
Me.TextBox6 = ""
Me.ComboBox5 = "Select"
Me.TextBox7 = ""
Me.TextBox8 = ""
Me.TextBox9 = ""
Me.TextBox10 = ""
Me.TextBox11 = ""
Me.TextBox12 = ""
Me.ComboBox6 = "Select"

Me.TextBox1.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

I have attached a picture of the form below.

Martin Liss

Please attach your workbook.

Norie

Try adding this code.

Private Sub OptionButton1_Click()
    If OptionButton1.Value = True Then
        ComboBox3.Visible = False
        ComboBox7.Visible = True
    End If
End Sub

Private Sub OptionButton2_Click()
    If OptionButton2.Value = True Then
        ComboBox3.Visible = True
        ComboBox7.Visible = False
    End If
End Sub

Open in new window

Fabrice Lambert

Well, well, well ..... *grab an evil tool*

- Start by giving your controls and variables meaningfull names, ObjectButton1, ObjectButton2, ComboBox3, ComboBox7 ect ... are nowhere near meaningfull.
- Did you put Option Explicit at op of your module ?
- Within a form, the me keyword is optional when refering to controls. A form know the controls it hold without needed to refer to itself.
- Give up hungarian notation, it provide nothing usefull.
- Your function is too long and do too many things at once, by respect to SRP, split it onto several small ones that do one thing, but do it well.

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Pleased to help