Count Form Button Depressions

Paul Black · 07-16-2006, 07:25 AM

Hi everyone,

I have a Form Button called "A". I would like to count how many times
it is pushed.
I also have a Form Button called "B". I would like the count of Form
Button "A" to reset to ZERO when Form Button "B" is pressed.

Thanks in Advance.
All the Best.
Paul

RB Smissaert · 07-16-2006, 07:45 AM

Make a public variable in a normal module (at the top), say
something like lCountButtonA, so:
Public lCountButtonA As Long

In form A have somehing like this:

Private Sub CommandButtonA_Click()
lCountButtonA = lCountButtonA + 1
End Sub

In form B have somehing like this:

Private Sub CommandButtonB_Click()
lCountButtonA = 0
End Sub

RBS

"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone,
>
> I have a Form Button called "A". I would like to count how many times
> it is pushed.
> I also have a Form Button called "B". I would like the count of Form
> Button "A" to reset to ZERO when Form Button "B" is pressed.
>
> Thanks in Advance.
> All the Best.
> Paul
>

Paul Black · 07-16-2006, 08:15 AM

Thanks for the reply RBS,

What I ideally want is the count of the depressions of Form Button "A"
to appear in Cell I22.
The code I am using for Form Button "A" is :-

Sub Testl()
Calculate
With Sheets("A").Range("H20")
.Value = .Value + Application.Sum(Sheets("A").Range("H18:J18"))
End With
End Sub

Thanks in Advance.
All the Best.
Paul

RB Smissaert wrote:
> Make a public variable in a normal module (at the top), say
> something like lCountButtonA, so:
> Public lCountButtonA As Long
>
> In form A have somehing like this:
>
> Private Sub CommandButtonA_Click()
> lCountButtonA = lCountButtonA + 1
> End Sub
>
> In form B have somehing like this:
>
> Private Sub CommandButtonB_Click()
> lCountButtonA = 0
> End Sub
>
>
> RBS
>
>
> "Paul Black" <[email protected]> wrote in message
> news:[email protected]...
> > Hi everyone,
> >
> > I have a Form Button called "A". I would like to count how many times
> > it is pushed.
> > I also have a Form Button called "B". I would like the count of Form
> > Button "A" to reset to ZERO when Form Button "B" is pressed.
> >
> > Thanks in Advance.
> > All the Best.
> > Paul
> >

RB Smissaert · 07-16-2006, 08:25 AM

OK, it will be the same.
Just replace the variable lCountButtonA with:
Cells(22, 9)

RBS

"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the reply RBS,
>
> What I ideally want is the count of the depressions of Form Button "A"
> to appear in Cell I22.
> The code I am using for Form Button "A" is :-
>
> Sub Testl()
> Calculate
> With Sheets("A").Range("H20")
> .Value = .Value + Application.Sum(Sheets("A").Range("H18:J18"))
> End With
> End Sub
>
> Thanks in Advance.
> All the Best.
> Paul
>
> RB Smissaert wrote:
>> Make a public variable in a normal module (at the top), say
>> something like lCountButtonA, so:
>> Public lCountButtonA As Long
>>
>> In form A have somehing like this:
>>
>> Private Sub CommandButtonA_Click()
>> lCountButtonA = lCountButtonA + 1
>> End Sub
>>
>> In form B have somehing like this:
>>
>> Private Sub CommandButtonB_Click()
>> lCountButtonA = 0
>> End Sub
>>
>>
>> RBS
>>
>>
>> "Paul Black" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi everyone,
>> >
>> > I have a Form Button called "A". I would like to count how many times
>> > it is pushed.
>> > I also have a Form Button called "B". I would like the count of Form
>> > Button "A" to reset to ZERO when Form Button "B" is pressed.
>> >
>> > Thanks in Advance.
>> > All the Best.
>> > Paul
>> >
>

Paul Black · 07-16-2006, 08:35 AM

Thanks RBS,

Unfortunately I am new to VBA and am unsure of how to incorporate this
into the existing code.

Thanks again.
All the Best.
Paul

RB Smissaert wrote:
> OK, it will be the same.
> Just replace the variable lCountButtonA with:
> Cells(22, 9)
>
> RBS
>
>
> "Paul Black" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the reply RBS,
> >
> > What I ideally want is the count of the depressions of Form Button "A"
> > to appear in Cell I22.
> > The code I am using for Form Button "A" is :-
> >
> > Sub Testl()
> > Calculate
> > With Sheets("A").Range("H20")
> > .Value = .Value + Application.Sum(Sheets("A").Range("H18:J18"))
> > End With
> > End Sub
> >
> > Thanks in Advance.
> > All the Best.
> > Paul
> >
> > RB Smissaert wrote:
> >> Make a public variable in a normal module (at the top), say
> >> something like lCountButtonA, so:
> >> Public lCountButtonA As Long
> >>
> >> In form A have somehing like this:
> >>
> >> Private Sub CommandButtonA_Click()
> >> lCountButtonA = lCountButtonA + 1
> >> End Sub
> >>
> >> In form B have somehing like this:
> >>
> >> Private Sub CommandButtonB_Click()
> >> lCountButtonA = 0
> >> End Sub
> >>
> >>
> >> RBS
> >>
> >>
> >> "Paul Black" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi everyone,
> >> >
> >> > I have a Form Button called "A". I would like to count how many times
> >> > it is pushed.
> >> > I also have a Form Button called "B". I would like the count of Form
> >> > Button "A" to reset to ZERO when Form Button "B" is pressed.
> >> >
> >> > Thanks in Advance.
> >> > All the Best.
> >> > Paul
> >> >
> >

Jim May · 07-16-2006, 08:40 AM

Sub Counter() 'Assigned to you're A Button
Dim CurrCount As Long
Range("A1").Value = Range("A1") + 1
End Sub

Sub ClearCount() 'Assigned to your B Button
Range("a1").Value = 0
End Sub

"Paul Black" <[email protected]> wrote in message
news:[email protected]:

> Hi everyone,
>
> I have a Form Button called "A". I would like to count how many times
> it is pushed.
> I also have a Form Button called "B". I would like the count of Form
> Button "A" to reset to ZERO when Form Button "B" is pressed.
>
> Thanks in Advance.
> All the Best.
> Paul

RB Smissaert · 07-16-2006, 08:45 AM

You can leave the:
Public lCountButtonA as Long
as it is or take it out.

In other places replace lCountButtonA with:
Cells(22, 9)
You may have to do:
Sheet1.Cells(22, 9) or whatever sheet that cell is on.

Nil else to be done.

RBS

"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Thanks RBS,
>
> Unfortunately I am new to VBA and am unsure of how to incorporate this
> into the existing code.
>
> Thanks again.
> All the Best.
> Paul
>
> RB Smissaert wrote:
>> OK, it will be the same.
>> Just replace the variable lCountButtonA with:
>> Cells(22, 9)
>>
>> RBS
>>
>>
>> "Paul Black" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks for the reply RBS,
>> >
>> > What I ideally want is the count of the depressions of Form Button "A"
>> > to appear in Cell I22.
>> > The code I am using for Form Button "A" is :-
>> >
>> > Sub Testl()
>> > Calculate
>> > With Sheets("A").Range("H20")
>> > .Value = .Value + Application.Sum(Sheets("A").Range("H18:J18"))
>> > End With
>> > End Sub
>> >
>> > Thanks in Advance.
>> > All the Best.
>> > Paul
>> >
>> > RB Smissaert wrote:
>> >> Make a public variable in a normal module (at the top), say
>> >> something like lCountButtonA, so:
>> >> Public lCountButtonA As Long
>> >>
>> >> In form A have somehing like this:
>> >>
>> >> Private Sub CommandButtonA_Click()
>> >> lCountButtonA = lCountButtonA + 1
>> >> End Sub
>> >>
>> >> In form B have somehing like this:
>> >>
>> >> Private Sub CommandButtonB_Click()
>> >> lCountButtonA = 0
>> >> End Sub
>> >>
>> >>
>> >> RBS
>> >>
>> >>
>> >> "Paul Black" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hi everyone,
>> >> >
>> >> > I have a Form Button called "A". I would like to count how many
>> >> > times
>> >> > it is pushed.
>> >> > I also have a Form Button called "B". I would like the count of Form
>> >> > Button "A" to reset to ZERO when Form Button "B" is pressed.
>> >> >
>> >> > Thanks in Advance.
>> >> > All the Best.
>> >> > Paul
>> >> >
>> >
>

Paul Black · 07-16-2006, 10:15 AM

Thanks for the reply Jim,

I left out the line of code ...

Dim CurrCount As Long

.... for "A" and incorporated the line of code ...

Range("A1").Value = Range("A1") + 1

.... which worked perfectly.
I incorporated the line of code for "B" ...

Range("a1").Value = 0

.... which also worked perfectly.

----------------------------------------------------------------------

Thanks for the reply RBS,

I will also try out your suggestion.

Thank you both.
All the Best.
Paul

RB Smissaert wrote:
> You can leave the:
> Public lCountButtonA as Long
> as it is or take it out.
>
> In other places replace lCountButtonA with:
> Cells(22, 9)
> You may have to do:
> Sheet1.Cells(22, 9) or whatever sheet that cell is on.
>
> Nil else to be done.
>
> RBS
>
> "Paul Black" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks RBS,
> >
> > Unfortunately I am new to VBA and am unsure of how to incorporate this
> > into the existing code.
> >
> > Thanks again.
> > All the Best.
> > Paul
> >
> > RB Smissaert wrote:
> >> OK, it will be the same.
> >> Just replace the variable lCountButtonA with:
> >> Cells(22, 9)
> >>
> >> RBS
> >>
> >>
> >> "Paul Black" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks for the reply RBS,
> >> >
> >> > What I ideally want is the count of the depressions of Form Button "A"
> >> > to appear in Cell I22.
> >> > The code I am using for Form Button "A" is :-
> >> >
> >> > Sub Testl()
> >> > Calculate
> >> > With Sheets("A").Range("H20")
> >> > .Value = .Value + Application.Sum(Sheets("A").Range("H18:J18"))
> >> > End With
> >> > End Sub
> >> >
> >> > Thanks in Advance.
> >> > All the Best.
> >> > Paul
> >> >
> >> > RB Smissaert wrote:
> >> >> Make a public variable in a normal module (at the top), say
> >> >> something like lCountButtonA, so:
> >> >> Public lCountButtonA As Long
> >> >>
> >> >> In form A have somehing like this:
> >> >>
> >> >> Private Sub CommandButtonA_Click()
> >> >> lCountButtonA = lCountButtonA + 1
> >> >> End Sub
> >> >>
> >> >> In form B have somehing like this:
> >> >>
> >> >> Private Sub CommandButtonB_Click()
> >> >> lCountButtonA = 0
> >> >> End Sub
> >> >>
> >> >>
> >> >> RBS
> >> >>
> >> >>
> >> >> "Paul Black" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Hi everyone,
> >> >> >
> >> >> > I have a Form Button called "A". I would like to count how many
> >> >> > times
> >> >> > it is pushed.
> >> >> > I also have a Form Button called "B". I would like the count of Form
> >> >> > Button "A" to reset to ZERO when Form Button "B" is pressed.
> >> >> >
> >> >> > Thanks in Advance.
> >> >> > All the Best.
> >> >> > Paul
> >> >> >
> >> >
> >

Paul Black · 07-16-2006, 02:40 PM

Hi,

I protected the sheet without using a password and it seems to work OK.
Can my code be improved please :-

Sub Test1()
ActiveSheet.Unprotect
Calculate
With Sheets("A").Range("H20")
.Value = .Value + Application.Sum(Sheets("A").Range("H18:J18"))
End With
ActiveSheet.Protect
End Sub

Thanks in Advance.
All the Best.
Paul

Paul Black wrote:
> Thanks for the reply Jim,
>
> I left out the line of code ...
>
> Dim CurrCount As Long
>
> ... for "A" and incorporated the line of code ...
>
> Range("A1").Value = Range("A1") + 1
>
> ... which worked perfectly.
> I incorporated the line of code for "B" ...
>
> Range("a1").Value = 0
>
> ... which also worked perfectly.
>
> ----------------------------------------------------------------------
>
> Thanks for the reply RBS,
>
> I will also try out your suggestion.
>
> Thank you both.
> All the Best.
> Paul
>
> RB Smissaert wrote:
> > You can leave the:
> > Public lCountButtonA as Long
> > as it is or take it out.
> >
> > In other places replace lCountButtonA with:
> > Cells(22, 9)
> > You may have to do:
> > Sheet1.Cells(22, 9) or whatever sheet that cell is on.
> >
> > Nil else to be done.
> >
> > RBS
> >
> > "Paul Black" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks RBS,
> > >
> > > Unfortunately I am new to VBA and am unsure of how to incorporate this
> > > into the existing code.
> > >
> > > Thanks again.
> > > All the Best.
> > > Paul
> > >
> > > RB Smissaert wrote:
> > >> OK, it will be the same.
> > >> Just replace the variable lCountButtonA with:
> > >> Cells(22, 9)
> > >>
> > >> RBS
> > >>
> > >>
> > >> "Paul Black" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > Thanks for the reply RBS,
> > >> >
> > >> > What I ideally want is the count of the depressions of Form Button "A"
> > >> > to appear in Cell I22.
> > >> > The code I am using for Form Button "A" is :-
> > >> >
> > >> > Sub Testl()
> > >> > Calculate
> > >> > With Sheets("A").Range("H20")
> > >> > .Value = .Value + Application.Sum(Sheets("A").Range("H18:J18"))
> > >> > End With
> > >> > End Sub
> > >> >
> > >> > Thanks in Advance.
> > >> > All the Best.
> > >> > Paul
> > >> >
> > >> > RB Smissaert wrote:
> > >> >> Make a public variable in a normal module (at the top), say
> > >> >> something like lCountButtonA, so:
> > >> >> Public lCountButtonA As Long
> > >> >>
> > >> >> In form A have somehing like this:
> > >> >>
> > >> >> Private Sub CommandButtonA_Click()
> > >> >> lCountButtonA = lCountButtonA + 1
> > >> >> End Sub
> > >> >>
> > >> >> In form B have somehing like this:
> > >> >>
> > >> >> Private Sub CommandButtonB_Click()
> > >> >> lCountButtonA = 0
> > >> >> End Sub
> > >> >>
> > >> >>
> > >> >> RBS
> > >> >>
> > >> >>
> > >> >> "Paul Black" <[email protected]> wrote in message
> > >> >> news:[email protected]...
> > >> >> > Hi everyone,
> > >> >> >
> > >> >> > I have a Form Button called "A". I would like to count how many
> > >> >> > times
> > >> >> > it is pushed.
> > >> >> > I also have a Form Button called "B". I would like the count of Form
> > >> >> > Button "A" to reset to ZERO when Form Button "B" is pressed.
> > >> >> >
> > >> >> > Thanks in Advance.
> > >> >> > All the Best.
> > >> >> > Paul
> > >> >> >
> > >> >
> > >