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

[bakerman]

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.

[snb]

or give them the same 'groupname'

[paul_pearson]

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

[bakerman]

Combobox issue resolved.

[paul_pearson]

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

[bakerman]

Try it this way.


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

[paul_pearson]

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

[bakerman]

1) Remove the Tags from XFLOW A+B+C and put next code on top of 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

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

[paul_pearson]

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

[bakerman]

Change this line

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

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

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

[paul_pearson]

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

[bakerman]

This covers all, i guess.

[paul_pearson]

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

[bakerman]

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

[paul_pearson]

Thanks bakerman for all your help....appreciated