Hi all,
I have a button on my worksheet that activates a userform for data entry. I
want to include on this button an event that will clear contents of cells
B7:B22 and G7:G22. Also on the same event I want a cell value (AK3) to
increment by +1. The number in AK3 is limited to 3 digits so when it gets to
999 it must go to 000 on the next activation.
Can somebody please help me with a code for achieving this.

Kind Regards
gregork











;

Hi Gregor,

Worksheets("Sheet1").Range("B7:B22,G7:G22").Clear

Worksheets("Sheet1").Range("AK3").
If Value = 999 Then
.Value = 0
Else
.Value = .Value + 1
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"gregork" wrote in message
...
Hi all,
I have a button on my worksheet that activates a userform for data entry.
I
want to include on this button an event that will clear contents of cells
B7:B22 and G7:G22. Also on the same event I want a cell value (AK3) to
increment by +1. The number in AK3 is limited to 3 digits so when it gets
to
999 it must go to 000 on the next activation.
Can somebody please help me with a code for achieving this.

Kind Regards
gregork











;

I think Bob meant

With Worksheets("Sheet1").Range("AK3")
.Parent.Range("B7:B22,G7:G22").Clear
If .Value = 999 Then
.Value = 0
Else
.Value = .Value + 1
End If
End With


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Just another way:

With Worksheets("Sheet1")
.Range("B7:B22,G7:G33").Clear
With .Range("A7")
.Value = (.Value + 1) Mod 1000
End With
End With


In article ,
"gregork" wrote:

Hi all,
I have a button on my worksheet that activates a userform for data entry. I
want to include on this button an event that will clear contents of cells
B7:B22 and G7:G22. Also on the same event I want a cell value (AK3) to
increment by +1. The number in AK3 is limited to 3 digits so when it gets to
999 it must go to 000 on the next activation.
Can somebody please help me with a code for achieving this.

Hi all,
Many thanks for the replies.Small problem : I'm not certain where I should
be putting this code. I have tried putting it in with the userform
initialise code but it's not working there.

Kind Regards
Gregork


"JE McGimpsey" wrote in message
...
Just another way:

With Worksheets("Sheet1")
.Range("B7:B22,G7:G33").Clear
With .Range("A7")
.Value = (.Value + 1) Mod 1000
End With
End With


In article ,
"gregork" wrote:

Hi all,
I have a button on my worksheet that activates a userform for data
entry. I
want to include on this button an event that will clear contents of
cells
B7:B22 and G7:G22. Also on the same event I want a cell value (AK3) to
increment by +1. The number in AK3 is limited to 3 digits so when it
gets to
999 it must go to 000 on the next activation.
Can somebody please help me with a code for achieving this.

Private Sub Commandbutton1_click()


With Worksheets("Sheet1")
.Range("B7:B22,G7:G33").Clear
With .Range("A7")
.Value = (.Value + 1) Mod 1000
End With
End With
Userform1.Show
End Sub

although no reason it should not work in the initialize event.

--
Regards,
Tom Ogilvy

gregork wrote in message
...
Hi all,
Many thanks for the replies.Small problem : I'm not certain where I should
be putting this code. I have tried putting it in with the userform
initialise code but it's not working there.

Kind Regards
Gregork


"JE McGimpsey" wrote in message
...
Just another way:

With Worksheets("Sheet1")
.Range("B7:B22,G7:G33").Clear
With .Range("A7")
.Value = (.Value + 1) Mod 1000
End With
End With


In article ,
"gregork" wrote:

Hi all,
I have a button on my worksheet that activates a userform for data
entry. I
want to include on this button an event that will clear contents of
cells
B7:B22 and G7:G22. Also on the same event I want a cell value (AK3)
to
increment by +1. The number in AK3 is limited to 3 digits so when it
gets to
999 it must go to 000 on the next activation.
Can somebody please help me with a code for achieving this.