Increase/Decrease Decimals

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
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. (y) [/list]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
Here is a macro that will increase the decimal position by one place. You can then attach the macro to a keystroke.

Code:
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

Board Regular
Joined
Aug 6, 2003
Messages
174
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? :p

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

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 6, 2003
Messages
174

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
I am sure it can be done, but I don't know about the easy part!
 

harvey_v

New Member
Joined
Apr 29, 2005
Messages
2

ADVERTISEMENT

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

New Member
Joined
Dec 16, 2010
Messages
1
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

New Member
Joined
Feb 21, 2011
Messages
14
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!!!!:biggrin::biggrin::biggrin:
 

BobSundquist

New Member
Joined
Oct 27, 2015
Messages
20
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,763
Members
417,109
Latest member
996

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