userform button click stores a variable (number of clicks)

**JayEmTee91** · 10-13-2011, 11:15 AM

I'm not sure if this is possible, or if there is a better way to do it...

What I would like to do is in my userform, use my command button that runs all of my code to store a variable that is the number of times it has been clicked.

With this number, I would like to use that to keep track of what the click number was and for that number what the selections in my listbox were.

The selections in my listbox are put into cells on the sheet the macro is on, and these cells are copied to the worksheet called Hist, and this is where I want the copied selections to be matched with a specific mouse click. So each mouse click is associated with one set of copied data. I hope that makes sense. I can post my code, but I just want to get an idea if this is possible or not.

Thank you!

**Leith Ross** · 10-13-2011, 01:03 PM

Hello JayEmTee91,

There a few ways to store the number of times the button is clicked. The easiest would to be use a cell on one the worksheets. To prevent the users from changing the value, you could hide the column.

Another way would be to use a worksheet just for this purpose and hide it as xlVeryHidden. The user will not be able to unhide the sheet but you will be able to access it from code.

If the program will remain on only computer, you could save the count to the registry using VBA methods GetSetting and SaveSetting. No special access rights are required to save or return a value from the registry with this code.

Lastly, you could save everything in a text file. The count number and the ListBox selections would all be together in one place. You could import this information from the file to worksheet and even create individual files for users, days of the month, etc.

**JayEmTee91** · 10-13-2011, 01:08 PM

I would like to take the route of storing the value in a cell. How would I go about doing so?

**Leith Ross** · 10-13-2011, 05:10 PM

Hello JayEmTee91,

Here is an example that you will need to adapt to your situation. This simply tracks the number of times the button has been clicked. The code assumes that CommandButton1 is the name of button the user is clicking.

UserForm Command Button Code

Private Sub CommandButton1_Click()

    With Worksheets("Sheet1").Range("A1")
        .Value = .Value + 1
    End With

End Sub

**JayEmTee91** · 10-19-2011, 03:13 PM

I could not get the way you suggested to work given my code, but the following code allows me to do what I want.

With each click in the userform, the counter and all the information in the click is moved to another sheet called history, and since the information that is copied occupies 2 rows, i want it to skip a row and then continue onto the one after that. Each click stores history based on the number.

The code that I have messes up on the first two clicks. The first click puts the items that were copied in the first spot I want, but then the second click overwrites the first clicks copy. The third click skips a line and copies the information correctly. I do not understand why that is. My code is below:

This code is for the counter...

Num = Sheets("Macro").Cells(7, 1).Value 'A1 change to suit
        If Sheets("Macro").Cells(7, 1).Value = "" Then
            Num = 1
            Sheets("Macro").Cells(7, 1).Value = Num
        Else: Sheets("Macro").Cells(7, 1).Value = Num + 1
        End If

This code is for the copy command.

 Set srang1 = Range(Cells(7, xlFirstCol), (Cells(xlLastRow, 2)))
        Set srang2 = Range(Cells(7, xlFirstCol + 1), (Cells(xlLastRow, xlFirstCol + 1)))
        Set srang3 = Range(Cells(7, xlFirstCol + 2), (Cells(xlLastRow, xlFirstCol + 2)))
        Set srang4 = Range(Cells(7, xlFirstCol + 3), (Cells(xlLastRow, xlFirstCol + 3)))
        Set srang5 = Range(Cells(7, xlFirstCol + 4), (Cells(xlLastRow, xlFirstCol + 4)))
        Set srang6 = Range(Cells(7, xlFirstCol + 5), (Cells(xlLastRow, xlFirstCol + 5)))
        Set srang7 = Range(Cells(7, xlFirstCol + 6), (Cells(xlLastRow, xlFirstCol + 6)))
        Set MyRange = Union(srang1, srang2, srang3, srang4, srang5, srang6, srang7)
        If Num = 1 Then
        MyRange.Copy Destination:=Sheets("Hist").Cells(3, 1)
        ElseIf Num > 1 Then
        MyRange.Copy Destination:=Sheets("Hist").Cells(CurPos(Num), 1)
        End If
Function xlFirstCol(Optional WorksheetName As String) As Long
     
     '  find the first populated column in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlFirstCol = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByColumns, xlNext).Column
    End With

End Function

Function xlFirstRow(Optional WorksheetName As String) As Long
     
     '  find the first populated row in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlFirstRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlNext).row
    End With
     
End Function

Function xlLastRow(Optional WorksheetName As String) As Long
     
     '    find the last populated row in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).row
    End With
     
End Function
Function xlLastCol(Optional WorksheetName As String) As Long

     '    find the last populated column in a worksheet
     
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByColumns, xlPrevious).Column
    End With
End Function

Function CurPos(Num)
CurPos = Num * 3
End Function

Any suggestions would be appreciated!