Results 1 to 7 of 7

Thread: Checkbox (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Checkbox (2000)

    Hi all,
    I have 4 checkboxs in a Userform that I would like get to work, what I would like is for when the command button is clicked, I would like to have the value of that box inputed into an Excel Spreadsheet.

    Here's what I have
    If Checkbox1.value = true then
    Activecell.value="C"
    Elseif
    If Checkbox2.value = true then
    activecell.value ="M"
    elseif
    If Checkbox3.value= true then
    activecell.value = "Y"
    If checkbox4.value=true then
    activecell.value="K"
    End if
    What I would like to see in the activecell is C,M,Y,K, what I get now is a Or what ever button the user selects.
    Can someone show me how to do that?

    Thanks,
    Darryl.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checkbox (2000)

    First of all:

    Why are you using checkboxes? This situation calls for four option buttons.

    You code looks a bit strange, I would have done it like this:

    If Checkbox1.value = true then
    Activecell.value="C"
    End If
    If Checkbox2.value = true then
    activecell.value ="M"
    End If
    If Checkbox3.value= true then
    activecell.value = "Y"
    End If
    If checkbox4.value=true then
    activecell.value="K"
    End if
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checkbox (2000)

    Sorry, but can you rephrase that last bit:<hr>"what I get now is a Or what ever button the user selects"<hr>
    I'm also assuming you're speaking of option (radio) buttons, where only one can be set to True?

    Alan

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Checkbox (2000)

    I agree with the previous threads. Option buttons would be better, as currently the user can select 3 of the 4 checkboxes, and the activecell will contain first the C then the Y then the K....etc. It simply over writes the current value.
    Rather use option buttons that allow only one to be selected. You can then loop through the group to test which one has a value!
    Regards,
    Rudi

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Checkbox (2000)

    OK,

    I tried the optionbuttons, but how do would you guys suggest I loop through it to extract the information needed?
    What I would like is for the user to click on the box or button, and as it is clicked, it inputs a string into the active cell, but if mulitple buttons are checked, it places the additional value into the same cell,instead of overwriting it. Is there a way to work around it, or should I simply put each value into a seperate cell?


    Laughing at the "wierd "code....I'm new to VBA and hope to learn as much as I can.
    Darryl

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checkbox (2000)

    You should use check boxes after all, since you want the user to be able to check multiple options. Try this:

    Dim strValue As String
    strValue = ""

    If Checkbox1.Value = True Then
    strValue = strValue & ",C"
    End If

    If Checkbox2.Value = True Then
    strValue = strValue & ",M"
    End If

    If Checkbox3.Value = True Then
    strValue = strValue & ",Y"
    End If

    If checkbox4.Value = True Then
    strValue = strValue & ",K"
    End If

    If Not (strValue = "") Then
    strValue = Mid(strValue, 2)
    End If

    ActiveCell.Value = strValue

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Checkbox (2000)

    Thanks Hans

    Thank all of you.

Posting Permissions

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