Click button to count checked Checkboxes on a Form. help?

[ExcellVBUser]

I have a UserForm1. my user will type their name in a textbox1
there are say 5 checkboxes labeled Item1 -Item5
Beside the checkboxes i have a button that i want to count the Items checked then store each in range b2-b21 on sheet1
there is also a Userform1.hide 'button'

I want it to check if it's a new user, so the Textbox1 will check row 1 for any old user names, if textbox is the same as any names in row1 then i want it to add the items to anything that might be under that name. If textbox1 is a new user then it checks for the next column without a username, puts the new username there, then fills in the items beneth it. Also if any items match the old users list to over write it, i don't want duplicate items under a user.

Can anyone please help me out here. ray:

 

[Norie]

I'm unclear about how your data is laid out.

Are the usernames in a column or a row?

If they aren't I would recommend that you change your layout.

I would have in row 1 column headings Username, Item1, Item2 ... Item5 in columns A-F.

Then you can put the usernames and items in the following rows.

 

[ExcellVBUser]

Re: Click button to count checked Checkboxes on a Form. help

Thanks for that tip Norie, I have changed my layout on sheet1 as
stated. Should i use a case statement to see if the checkboxes are checked?

 

[Norie]

I'm currently looking at your request.

Another tip is that you can cycle through all the controls on a form like this:

Dim ctl As MSForms.Control

     For Each ctl In Me.Controls ' Me is a reference to the userform
          ' do something with the ctl - say enter it's value on a sheet
     Next

Because you'll have other controls on the form you'll probably want to use some sort of If to identify the checkboxes.

Perhaps something like this:

Dim ctl As MSForms.Control
For Each ctl In Me.Controls ' Me is a reference to the userform
    If TypeOf ctl Is MSForms.CheckBox Then
        ' do something with the control ctl
    End If
Next

 

[Norie]

ADVERTISEMENT

The following code assumes you have 5 checkboxes (chkItem1, chkItem2 etc), a textbox (txtUserName) and a command button (cmdAddData).

It also assumes that you have the data organised as mentioned in my other post.

Private Sub cmdAddData_Click()
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim UserRow As Long
Dim I As Long
Dim boolFound As Boolean
    
    Set ws = ActiveSheet
    
    LastRow = ws.Range("A65536").End(xlUp).Row
    
    I = 1
    
    boolFound = False
    
    While I <= LastRow And Not boolFound
        Set rng = ws.Range("A" & LastRow)
        boolFound = rng.Text = txtUserName.Text
        I = I + 1
    Wend
    
    UserRow = I
    
    Set rng = ws.Range("A" & UserRow)
    
    rng = txtUserName
        
    For I = 1 To 5
        rng.Offset(0, I) = Me.Controls("chkItem" & I)
    Next I
    
End Sub

I don't know if this is exactly what you want but it may give you some ideas.

 

[ExcellVBUser]

Re: Click button to count checked Checkboxes on a Form. help

This is definetly the right track. It seems that the new entries are always copied to a new row on first click, then goes to back to the first row of data and over writes it on second click. Is it possible to have txtUserName check old names and change the data. I need it to display the name of the checkbox in the cells beside the name. I need to be able to type name in, check if there's a old record of that name, then update the data, keeping the items that might be with that name.

Thanks for your help!

marc

I won't be able to check back for a few hours, thanks for any extra help.

 

[Norie]

ADVERTISEMENT

I thought it was working, but then again I didn't test it very much.

I've changed it. Does this work?

Private Sub cmdAddData_Click()
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim UserRow As Long
Dim I As Long
Dim boolFound As Boolean
    
    Set ws = ActiveSheet
    
    LastRow = ws.Range("A65536").End(xlUp).Row
    
    I = 1
    
    boolFound = False
    
    While I <= LastRow And Not boolFound
        Set rng = ws.Range("A" & I)
        boolFound = rng.Text = txtUserName.Text
        I = I + 1
    Wend
    
    If boolFound Then
        UserRow = I - 1
    Else
        UserRow = I
    End If
    
    Set rng = ws.Range("A" & UserRow)
    
    rng = txtUserName
        
    For I = 1 To 5
        rng.Offset(0, I) = Me.Controls("chkItem" & I)
    Next I
    
End Sub

 

[ExcellVBUser]

Re: Click button to count checked Checkboxes on a Form. help

Hey Norie,
I've been playing with the code you provided, but i'm still not getting it to check for a empty row, or a matching record.

Dim ctl As MSForms.Control

Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim UserRow As Long
Dim I As Long
Dim boolFound As Boolean

Private Sub cmdAddData_Click()
For Each ctl In Me.Controls ' Me is a reference to the userform
    If TypeOf ctl Is MSForms.CheckBox Then
        ' do something with the control ctl
    Set ws = ActiveSheet
    LastRow = ws.Range("A65536").End(xlUp).Row
    I = 1
    boolFound = False
        While I <= LastRow And Not boolFound
          Set rng = ws.Range("A" & LastRow)
          boolFound = rng.Text = txtUserName.Text
          I = I + 1
        Wend
      If boolFound = True Then
        UserRow = I + 1
      Else
        UserRow = I
      End If
    Set rng = ws.Range("A" & UserRow)
      If rng = txtUserName Then
        For I = 1 To 5
            rng.Offset(0, I) = Me.Controls("chkItem" & I)
        Next I
      Else
        rng = txtUserName
      End If
    End If
Next
End Sub

Do i have the right idea here or am i totally off base?

 

[Norie]

Did you try the code from my last post?

Or are you adapting the code from my earlier post?

 

[ExcellVBUser]

Re: Click button to count checked Checkboxes on a Form. help

yes I tried your code just as you wrote it both times, then used the second code you gave me to modify into the 'mess' you see up there, should i go back to your code and start from there, or is anything i added worth keeping?