Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: changing code from using Combobox to optionbuttons

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12

    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
    Attached Files Attached Files

  2. #2
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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
    Attached Files Attached Files

  4. #4
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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.

  5. #5
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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
    Attached Files Attached Files

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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 !
    Last edited by snb; 09-02-2013 at 04:55 PM.

  7. #7
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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
    Attached Files Attached Files
    Last edited by paul_pearson; 09-02-2013 at 06:11 PM.

  8. #8
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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

  9. #9
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    This works for me.
    Attached Files Attached Files

  10. #10
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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
    Attached Files Attached Files

Similar Threads

  1. Replies: 8
    Last Post: 08-17-2013, 04:03 AM
  2. Changing Picture Using Data Validation
    By jeff in forum Excel Help
    Replies: 3
    Last Post: 08-03-2013, 09:39 AM
  3. adding entries into combobox with code
    By paul_pearson in forum Excel Help
    Replies: 1
    Last Post: 07-23-2013, 01:01 PM
  4. Changing Slideshow viewing mode to kiosk using vba
    By Times in forum Powerpoint Help
    Replies: 1
    Last Post: 05-10-2013, 12:37 AM
  5. Changing Hyperlink To Another Sheet By Changing Value Of Cell
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-30-2011, 11:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •