changing code from using Combobox to optionbuttons

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

For Machine_Stoppage.

Code:

---

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

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

Why did you change this part ??

Code:

---

    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.

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

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

Code:

---

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 !

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

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

This works for me.

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

My guess the best option would be to put them in 1 frame so you select either one of them. Otherwise you should put an extra button to enable both frames when you change your mind.

or give them the same 'groupname'

Thanks
I did have a version where I placed all in the 1 frame...I am stilling working on it but it does work on some features.The issues I am having is if I select Internal and its selections from the combobox`s it works as it renders External combobox`s disabled.If i then change to External is keeps the selections in the combobox`s also from Internal so it ends up transferring both Internal and External combobox`s......if I select Internal and its combobox`s only it works fine......same for External

I also have userform Production and Planned Stop now working

How do I give the same Groupname.....is this in properties?

Thanks

Combobox issue resolved.

Thanks bakerman
Works....if i select Internal or External can the Time textbox`s also be disabled (entry deleted) on the 1 not selected.
If changing from say Internal to External it removes the entries in the Combobox`s but not the Textbox.......i will attempt to resolve this so would i need to add code in the Optionbutton4 and 5 parts of the code

Thanks again

Try it this way.

Code:

---

Private Sub OptionButton4_Click()

    For i = 2 To 6
        With Me("Combobox" & i)
            .Value = vbNullString
            .Enabled = IIf(i <= 4, True, False)
        End With
        If i = 2 Or i = 3 Then
            With Me("Textbox" & i)
                .Text = vbNullString
                .Enabled = IIf(i = 2, True, False)
            End With
        End If
    Next
   
End Sub

Private Sub OptionButton5_Click()

    For i = 2 To 6
        With Me("Combobox" & i)
            .Value = vbNullString
            .Enabled = IIf(i <= 4, False, True)
        End With
        If i = 2 Or i = 3 Then
            With Me("Textbox" & i)
                .Text = vbNullString
                .Enabled = IIf(i <> 3, False, True)
            End With
        End If
    Next
   
End Sub

---

Thanks for all your help bakerman...the code above worked

I did find 2 errors...i used the Tag property in the userform for the Combobox`s and Textbox`s and they work...i used the Tag Property for the Optionbuttons for XFLOW A+B+C also but if i transfer the userform without selecting XFLOW A or B or C it gives a Runtime error...i thought the Tag property would stop this error...

Also if i do not select External or Internal i can then select all the Combobox`s and Textbox`s in the frame......is there a way to force either Internal or External to be selected

Thanks

Paul

1) Remove the Tags from XFLOW A+B+C and put next code on top of CommandButton1_Click.

Code:

---

    For Each ctl In Frame1.Controls
        If TypeName(ctl) = "OptionButton" Then
            If ctl.Value = False Then x = x + 1
        End If
    Next
    If x = 3 Then MsgBox "Select a machine before you write data": Exit Sub

---

2) In designmode set the Enabled-property of the CB's and both TB's to False.

Thanks bakerman
Added the code (possibly in the wrong place) and set the Enabled-property of the CB's and both TB's to False

If i select date and machine and press Transfer Button it messagebox says "Select a Part"...i press OK and it give a Runtime Error 2110.....

Could this be where i placed the code as you mentioned on top of CommandButton1_Click...i could not get it to place above the CommandButton1_Click which may cause the error

Thanks again

Change this line

Code:

---

If ctl.Value = vbNullString Then MsgBox ctl.Tag: ctl.SetFocus: Exit Sub

---

to this line

Code:

---

If ctl.Value = vbNullString Then MsgBox ctl.Tag: Exit Sub

---

The error occured because you can't set focus to a disabled object.

Thanks bakerman

That solved.....no error but if i select External and it combobox`s ,fill in a comment and then Transfer Data a messagebox asks for me to select a Part which is disabled in the Internal section......would this be from Tags in the properties....if i remove this this it will not be available when i select Internal option

It works fine if i select the Internal option as it transfers the data correctly,it is only if i select External this happens

Thanks again

This covers all, i guess.

Thanks again bakerman

All works well and i guess you are most likely getting tired of this thread but 1 last go

If i enter a date and machine i can send data without it having all the fields with entries....it only asks for a Comment....

If this can be changed i think it is finally finished

Thanks again

Code:

---

Private Sub CommandButton1_Click()

    For Each ctl In Frame1.Controls
        If TypeName(ctl) = "OptionButton" Then
            If ctl.Value = False Then x = x + 1
        End If
    Next
    If x = 3 Then MsgBox "Select a machine before you write data": Exit Sub
    For Each ctl In Frame2.Controls
        If TypeName(ctl) = "OptionButton" Then
            If ctl.Value = False Then y = y + 1
        End If
    Next
    If y = 2 Then MsgBox "Select Internal or External before you write data": Exit Sub
    'check user input
    For Each ctl In Me.Controls
        If ctl.Tag <> vbNullString And ctl.Enabled Then
            If ctl.Value = vbNullString Then MsgBox ctl.Tag: 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, TextBox2.Text, IIf(OptionButton4, TextBox1.Text, ""), _
            ComboBox5.Value, ComboBox6.Value, TextBox3.Text, 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
    For i = 2 To 6
        Me("Combobox" & i).Enabled = False
        If i = 2 Or i = 3 Then Me("Textbox" & i).Enabled = False
    Next
    End Sub

---

Thanks bakerman for all your help....appreciated