DP jog macro.

[Aaron]

Hey gurus,

I have had a prob for a while and thought I had sussed it but have now
realised im not quite there.

ActiveSheet.Unprotect
Range("Q16").Select
Selection.NumberFormat = "0.0"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

I have tied this macro to a button on the worksheet, but all it is
doing is setting the cell to 0.0 dp.

What I want is a jog function, where I can press it and see the target
cell dp go up and up to 0.0, 0.00, 0.000, 0.0000 etc each time.

Can someone please help with this?

Cheers,

Aaron.

 

[Bob Phillips]

ActiveSheet.Unprotect
With Range("Q16")
If .NumberFormat = "0" Or .NumberFormat = "General" Then

.NumberFormat = "0.0"
Else
.NumberFormat = .NumberFormat & "0"
End If
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

 

[Dennis]

Dim strTemp as String

ActiveSheet.Unprotect
Range("Q16").Select
If Selection.NumberFormat <> "0.0" Then
strTemp = "0."
Else
strTemp = Selection.NumberFormat
End If
strTemp = strTemp & "0"
Selection.NumberFormat = strTemp
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[Dennis]

Slight problem with mine, but Bobs will work OK

Dim strTemp as String

ActiveSheet.Unprotect
Range("Q16").Select
If Selection.NumberFormat <> "0.0" Then
strTemp = "0."
Else
strTemp = Selection.NumberFormat
End If
strTemp = strTemp & "0"
Selection.NumberFormat = strTemp
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

 

[Aaron]

Slight problem with mine, but Bobs will work OK

Thanks Bob and Dennis I will add this code asap. It will solve a
problem I have had for a year and a half!

Cheers,

Aaron.

 

[Aaron]

Hi,

As well as a jog up I need also to have a jog down macro,

I tried to make this work for the dp reducing jog by removing trailing
dp like 0.00000 to 0.0000 to 0.000 to 0.00 etc but I cant seem to
modify Bobs code to get it to work.

This is as far as I got.

I need an up and a down one to go from 0 up to 0.00000 and from
0.00000 to 0 regardless of where it is at the time when the up or down
macro tied button is pressed.

ActiveSheet.Unprotect
With Range("Q16")
If .NumberFormat = "0.00000" Or .NumberFormat = "General" Then

.NumberFormat = "0.0000"

if
.NumberFormat = "0.0000" Or .NumberFormat = "General" Then

.NumberFormat = "0.000"

if
.NumberFormat = "0.000" Or .NumberFormat = "General" Then

.NumberFormat = "0.00"

if
.NumberFormat = "0.00" Or .NumberFormat = "General" Then

.NumberFormat = "0.0"

if
.NumberFormat = "0.0" Or .NumberFormat = "General" Then

.NumberFormat = "0"

End If

End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

More help please!

Cheers,

Aaron.