Include a counter in a VBA macro

[RCONDADO]

Dear all,

I'm using a macro just to suffle a sequence of numbers and then assign that to a button (attached), as follow:

Sub Embaralha_seleção()
    Dim numm()
    numm = Range("A1:A100")
    ct = UBound(numm, 2)
    lt = UBound(numm, 1)
    ReDim dex(1 To lt * ct)
    n = 0
    For h = 1 To ct
        For v = 1 To lt
            If numm(v, h) & " " <> " " Then
                n = n + 1
                dex(n) = numm(v, h)
                numm(v, h) = ""
            End If
        Next
    Next
    t = 1
    For h = 1 To ct
        For v = 1 To lt
volta:
            Randomize
            vvv = Int((n * Rnd) + 1)
            If dex(vvv) = "" And t <= n Then
                GoTo volta
            Else
                t = t + 1
                numm(v, h) = dex(vvv)
                dex(vvv) = ""
            End If
        Next
    Next
    Range("A1:A100") = numm
End Sub

It's working properly, but now I need to include two rotines, as follow:

1. Include a counter to count how many times the button was pressed; and
2. Save the workbook everytime that the button was pressed.

Please, could you help me?
Thank you and regards!

R. Condado.

 

[steve the fish]

You can store a value in a hidden (or very hidden) sheet and increment that at each press of the button. The save depends on if the book has already been saved previously and when you want it to save. Probably something like:

Sheets("VeryHidden").Range("A1").Value = Sheets("VeryHidden").Range("A1").Value + 1
ThisWorkbook.Save

 

[Rick Rothstein]

The code you posted simply randomizes the values in range A1:A100, correct? If so, you can use this shorter routine to do the same...

Sub Embaralha_seleção()
  Dim Cnt As Long, RandIdx As Long, Temp As Variant, Arr As Variant
  Arr = Range("A1:A100")
  Randomize
  For Cnt = UBound(Arr) To 1 Step -1
    RandIdx = Int(Cnt * Rnd + 1)
    Temp = Arr(RandIdx, 1)
    Arr(RandIdx, 1) = Arr(Cnt, 1)
    Arr(Cnt, 1) = Temp
  Next
  Range("A1:A100") = Arr
End Sub

The above code does not address your two additional questions. It is unclear what you want done with the count of the number of times the button was pressed. Is it to be saved somewhere or incorporated into the name used to save the workbook under, or used in some other way. Also, in addition, do you want the button count to persist from closing of the current workbook to the next time the workbook is open or should the button count start back at zero each time the workbook is open. Please clarify these for us?

 

[RCONDADO]

You can store a value in a hidden (or very hidden) sheet and increment that at each press of the button. The save depends on if the book has already been saved previously and when you want it to save. Probably something like:

Sheets("VeryHidden").Range("A1").Value = Sheets("VeryHidden").Range("A1").Value + 1
ThisWorkbook.Save

Hi @steve the fish,
Yes, this is exactly what I need to do. I just need the right code to do that.

 

[RCONDADO]

The code you posted simply randomizes the values in range A1:A100, correct? If so, you can use this shorter routine to do the same...

Sub Embaralha_seleção()
  Dim Cnt As Long, RandIdx As Long, Temp As Variant, Arr As Variant
  Arr = Range("A1:A100")
  Randomize
  For Cnt = UBound(Arr) To 1 Step -1
    RandIdx = Int(Cnt * Rnd + 1)
    Temp = Arr(RandIdx, 1)
    Arr(RandIdx, 1) = Arr(Cnt, 1)
    Arr(Cnt, 1) = Temp
  Next
  Range("A1:A100") = Arr
End Sub

The above code does not address your two additional questions. It is unclear what you want done with the count of the number of times the button was pressed. Is it to be saved somewhere or incorporated into the name used to save the workbook under, or used in some other way. Also, in addition, do you want the button count to persist from closing of the current workbook to the next time the workbook is open or should the button count start back at zero each time the workbook is open. Please clarify these for us?

Hi @Rick Rothstein,
Thank you for your comments.
Actually, this image attached is the button that I created an I just have to include a line bellow to inform "Count" and how many times the button was pressed and I want the button count to persist from closing of the current workbook to the next time the workbook is open.
Do you think is it possible?

 

[RCONDADO]

You can store a value in a hidden (or very hidden) sheet and increment that at each press of the button. The save depends on if the book has already been saved previously and when you want it to save. Probably something like:

Sheets("VeryHidden").Range("A1").Value = Sheets("VeryHidden").Range("A1").Value + 1
ThisWorkbook.Save

@steve the fish

It worked now with your code.
Many thanks for your help!

 

[RCONDADO]

The code you posted simply randomizes the values in range A1:A100, correct? If so, you can use this shorter routine to do the same...

Sub Embaralha_seleção()
  Dim Cnt As Long, RandIdx As Long, Temp As Variant, Arr As Variant
  Arr = Range("A1:A100")
  Randomize
  For Cnt = UBound(Arr) To 1 Step -1
    RandIdx = Int(Cnt * Rnd + 1)
    Temp = Arr(RandIdx, 1)
    Arr(RandIdx, 1) = Arr(Cnt, 1)
    Arr(Cnt, 1) = Temp
  Next
  Range("A1:A100") = Arr
End Sub

The above code does not address your two additional questions. It is unclear what you want done with the count of the number of times the button was pressed. Is it to be saved somewhere or incorporated into the name used to save the workbook under, or used in some other way. Also, in addition, do you want the button count to persist from closing of the current workbook to the next time the workbook is open or should the button count start back at zero each time the workbook is open. Please clarify these for us?

@Rick Rothstein,
Thank you, your shorter code worked perfecly.

 

[Peter_SSs]

Here is another short way to shuffle the 100 rows. Shorter if column B is empty as two lines of code can be removed.

Sub Shuffle()
  Columns("B").Insert '<- delete if column B is empty
  Range("B1:B100").Formula = "=rand()"
  Range("A1:B100").Sort Key1:=Range("B1"), Header:=xlNo
  Columns("B").Delete '<- delete if column B is empty
End Sub

 

[RCONDADO]

Here is another short way to shuffle the 100 rows. Shorter if column B is empty as two lines of code can be removed.

Sub Shuffle()
  Columns("B").Insert '<- delete if column B is empty
  Range("B1:B100").Formula = "=rand()"
  Range("A1:B100").Sort Key1:=Range("B1"), Header:=xlNo
  Columns("B").Delete '<- delete if column B is empty
End Sub

@Peter_SSs Thank you

 

[Peter_SSs]

@Peter_SSs Thank you

You're welcome.