Increase/Decrease Decimals

[BuddieB]

I know that there is a button that already does this, but is there a way to assign this command to a keystroke?

Or better yet, does anyone have some VBA that mimics the same action of the increase/decrease decimal buttons?

I'm just not a mouse guy and I wanted to assign the command to a couple key strokes.

Thanks for your help. [/list]

 

[Joe4]

Here is a macro that will increase the decimal position by one place. You can then attach the macro to a keystroke.

Sub IncreaseDecimal()

    Dim myFormat As String
    myFormat = Selection.NumberFormat
    If (Len(myFormat) > 1) And (Mid(myFormat, 2, 1) = ".") Then
        myFormat = myFormat & "0"
    Else
        myFormat = "0.0"
    End If
    Selection.NumberFormat = myFormat

End Sub

From this, see if you can also build one to reduce the decimal places (hint: think Left(myFormat,Len(myFormat)-1):

 

[BuddieB]

I actually took a couple stabs at it and was able to come up with one that works for a typical number, but it doesn't work for cases when I am dealing with % or $ formatted cells. The buttons DO handle this, any chance that someone can come up with some code that will match the behavior of the buttons for all cases?

Here's what I have already for normal decimal values:

Sub DecimalIncrease()
'
' DecimalIncrease Macro
' Increases the number of digits after the decimal

    Curformat = ActiveCell.NumberFormat
    
    If Curformat = "General" Then Selection.NumberFormat = "0"
    If Curformat = "0" Then Selection.NumberFormat = "0.0"
    
    If Mid(Curformat, 2, 1) = "." Then
        Selection.NumberFormat = Curformat & "0"
    End If
    
End Sub

 

[Joe4]

As you can see, this can get quite complicated, as there are many different formats to consider (which I failed to do in my first post).

Rather then re-create the wheel, if you don't like the mouse, then how about menu shortcuts (O, E, Tab, etc.) to go into the Format menu?

 

[BuddieB]

oh I know all about the ALT + letters to get to the menus. I was just hoping that there might be an easy way to link an existing XL button to a keystroke.

 

[Joe4]

I am sure it can be done, but I don't know about the easy part!

 

[harvey_v]

I would also be very interested in an answer to this question. I saw in another thread that there is a way to enable and disable the command bar buttons on the toolbars. In the same vein, is there a way to execute them using a VBA macro?

 

[FouadAhmed]

Easy.

to increase decimals:

sub Increase_Decimal()
Application.CommandBars.FindControl(ID:=398).Execute
end sub


To decrease decimals:

Sub Decrease_Decimal()
Application.CommandBars.FindControl(ID:=399).Execute
End Sub

 

[level3ninja]

Easy.

to increase decimals:

sub Increase_Decimal()
Application.CommandBars.FindControl(ID:=398).Execute
end sub


To decrease decimals:

Sub Decrease_Decimal()
Application.CommandBars.FindControl(ID:=399).Execute
End Sub

LIFE SAVER!!!!

 

[BobSundquist]

Here is an improved version that works with a field with "General" format initially. This used to work with Excel 2003; Microsoft broke it sometime between then and Excel 2013:

Sub IncreaseDecimal()
If InStr(Selection.NumberFormat, "General") Then
If InStr(ActiveCell.Text, ".") Then
n = Len(ActiveCell.Text) - InStr(ActiveCell.Text, ".")
Else
n = 0
End If
n = n + 1
FormatString = "0."
While n > 0
FormatString = FormatString & "0"
n = n - 1
Wend
Selection.NumberFormat = FormatString
Else
Application.CommandBars("Formatting").Controls("Increase Decimal").Execute
End If
End Sub