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:

 

[ExcellVBUser]

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

Hello again,
i went back to this code you wrote first.

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

This finds a new row for a new user, but i can't updated an old user if they get more items on there second time checking boxes. moreover if i hit the button twice it over writes the frist user data. Can you explain a little how this is working? I'm not understanding what some of this is doing.
thanks
marc

 

[Norie]

marc

Have you tried the following code which I posted?

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

Maybe i should run now You going to get a good kick out of this! when i changed the layout i made a copy of it and pasted it below on row 50!! it was catching it there, not filling from the top. Now that i've removed the copy, and placed the code. It works like a charm! now to get the 'True's to = chkItem.caption and 'False' = " "


:wink:

 

[Norie]

Try this alteration:

For I = 1 To 5 
     If Me.Controls("chkItem" & I) Then
        rng.Offset(0, I) = Me.Controls("chkItem" & I).Caption
     Else
        rng.Offset(0, I) = ""
     End If
Next I

 

[ExcellVBUser]

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

Truly a master, thanks for your help!

if i stop the range short at A10, and the whole range is full, how can i have a msgbox pop up saying 'no new users'

another newb=Marc

 

[ExcellVBUser]

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

Hey again Norie,
Let me just thank you for all the help you been on this checkbox thing, if you don't mind i have a few more things i need help on. How can the username be kept in column 'A' and move the items a few columns?
i have other data about the users in the next few columns that get entered via another control. Do i have to make another worksheet for Items? Is there some sort of If statement i can wrap this code in?

    LastRow = ws.Range("A10").End(xlUp).Row

marc

 

[Norie]

marc

Can you explain your last post further? I'm not 100% sure what you want.

If you want to put the items in different columns you need to change the Offset part of this:

For I = 1 To 5 
     rng.Offset(0, I) = Me.Controls("chkItem" & I) 
Next I

Currently it puts Item1 1 column to the right of column A, Item1 2 column to the right of column A.

The no of columns to the right (the offset) is determined by I.

If you wanted to start putting the items from column E say this should work:

For I = 1 To 5 
     rng.Offset(0, I + 3) = Me.Controls("chkItem" & I) 
Next I

 

[ExcellVBUser]

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

That's it! You have been a great help with this little hurdle. Thank you very much.


Marc