changing code from using Combobox to optionbuttons

[paul_pearson]

Hi

The code worked fine while using Combobox`s but I prefer to use Optionbuttons in a few changes.I searched but have not worked out how to change the code from combobox to optionbutton

1. Userform Machine Stoppage - use optionbutton to select machine instead of Combobox2
Internal and External optiobuttons used instead of Combobox3

2. Userform Planned Stop - use optionbutton to select Machine instead of Combobox2

3. Userform Production- use optionbutton to select Machine instead of Combobox2

Thanks

Paul

[bakerman]

For Machine_Stoppage.

Private Sub CommandButton1_Click()
    'check user input
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub
        End If
    Next
    For I = 1 To 3
        If Me("OptionButton" & I) Then whatsheet = Me("OptionButton" & I).Caption
    Next
    'write data to worksheet
    With Sheets(whatsheet)
       .Unprotect Password:="abc"
       .Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Resize(, 17) = Array(ComboBox1.Value, , , , , , , , _
            ComboBox2.Value, ComboBox3.Value, ComboBox4.Value, , IIf(OptionButton4, TextBox1.Text, ""), _
            ComboBox5.Value, ComboBox6.Value, , IIf(OptionButton5, TextBox1.Text, ""))
        .Protect Password:="abc"
    End With
    'Clear all fields
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
        If TypeName(ctl) = "OptionButton" Then ctl.Value = False
    Next
End Sub

I guess you can figure it out now for the other UF

[paul_pearson]

Thank you bakerman
I made a few changes which at least allowed me to open the userform.The date now appears in Combobox1...i deleted some code from the Post#1 sheet...I am still not there with the optionbuttons...i have done search on net

What books do you recommend for learning VBA?

Thanks for your help

Paul

[bakerman]

Why did you change this part ??

    For I = 1 To 3
        If Me("OptionButton" & I) Then Sheets("XFLOW A,XFLOW B,XFLOW C") = Me("OptionButton" & I).Caption
    Next
    'write data to worksheet
    With Sheets("XFLOW A,XFLOW B,XFLOW C")

The purpose of the variable 'whatsheet' is to store the OptionButtons caption so you can use it in the With sheets(whatsheet) to write to the correct sheet.

[paul_pearson]

I though whatsheet is if you were asking me what sheets...another point learnt

I am not sure with the Private Sub category_select()....i did change the comboboxs around but they are not visible in the userform....

I will keep trying

Thanks

Paul

[snb]

You'd better remove all merged cells.
in the userform machine stoppage this will suffice in the initialize event:

Private Sub UserForm_Initialize()
    ComboBox1.List = [transpose(text(today()-3+row(1:5),"dd-mmm-yyyy"))]
    
    sn = Sheets("INFO").Cells(3, 3).CurrentRegion.Offset(2)
    For j = 2 To 6
      Me("combobox" & j).List = Application.Index(sn, 0, j - 1)
    Next
End Sub

NB posting a non protected sample workbook would help the helpers a lot !

[paul_pearson]

thank you
password = abc

The info is now available in the userform...when I select Internal I need the External Option unavailable and visa versa....for this to work would I need both the Internal and External optionbuttons in the same Frame?

Thanks again

[paul_pearson]

If Internal optionbutton selected then External optionbutton and its comboboxs not available and if External optionbutton selected then Internal optionbutton and its comboboxs are not available
Thanks

[bakerman]

This works for me.

[paul_pearson]

Thanks Bakerman

I added a couple of text box`s to the userform and changed in the code...the data transfers to the correct cells...your code change for the Optionbutton works well but my only change is how can the code be changed because if I select say Internal by mistake I cannot then select External unless I close the userform and re-open then select External... say if I select Internal but then change my mind could I then select External (and visa-versa) .. I want to be able to select 1 of the Optionbuttons but if I change my mind then I can select the other Optionbutton....the combobox`s still only work though with the selected Optionbutton

Thanks