I would like to know how to set my variable Cont to 0 after every click of a button in a different macro(recorded).

shulman

New Member
Joined
Mar 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
My code is from a website online. All that it is supposed to do is add 1 to the value of E2 every time the user changes A2. I wanted it to reset E2 to zero every time the user clicked a button.
VBA Code:
Dim Cont As Integer
'Integer means no decimals
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rnge As Range, Cll As Range
On Error Resume Next
If Target = Range("A2") Then
Cont = Cont + 1
'If the user clicked this cell then the variable "Cont" will change to Cont+1
       Range("E2").Value = Cont
'The cell E2 needs updating to the new value
End If
'EXPERIMENTAL: If Button is clicked End If statement & clear E2

   Application.EnableEvents = False
   Set Rnge = Application.Intersect(Target.Dependents, Me.Range("A2"))
   If Not Rnge Is Nothing Then
        Cont = Cont + 1
        Range("E2").Value = Cont
   End If
    Application.EnableEvents = True
End Sub

1616095989322.png
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

If I understand you correctly, you have a lot of unnecessary stuff there.

Here is all that you need for your Worksheet_Change event procedure code to increase the value in E2 by 1 every time A2 is updated:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("E2").Value = Range("E2").Value + 1
    End If

End Sub

And then to reset the value in E2 to 0, all you need is this one line code:
VBA Code:
Sub MyClearValue()
    Range("E2") = 0
End Sub
and then assign that code to your button.
 

shulman

New Member
Joined
Mar 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I want the macro to only record changes to cell E2 when cell A2 is has a changed value. I don't want the number to update in E2 every time I delete what was in A2. Also I want the two (A2 and E2) to change to zero at the button click if possible.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
I want the macro to only record changes to cell E2 when cell A2 is has a changed value. I don't want the number to update in E2 every time I delete what was in A2.

OK, this amendment will ignore it if the change results A2 being cleared:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        If Range("A2") <> "" Then Range("E2").Value = Range("E2").Value + 1
    End If

End Sub

Also I want the two (A2 and E2) to change to zero at the button click if possible.
I bet you could have figured this one out yourself! It is simply a matter of adding another like very similar to the first:

VBA Code:
Sub MyClearValue()
    Range("A2") = 0
    Range("E2") = 0
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top