Setting the decimal points for a range through a form

[ruud29]

Hello,

I would like to have users to set the decimalpoints (number format) of a range through a form in excel.
I tried this by recording a macro first then copying this piece of code into a module, but the numberformat is set to the number entered in de form.
For example: if the user enters a 1 then the all the values in the range are displayed as 1.
Can somebody post a pice of code here to make this work?

Thanks so far!

 

[Zathras]

Here is one way:
Insert a user form with one list box and one pushbutton.
Put this in the code window for the form:

======================================================

Option Explicit

Private Sub lbDecimals_Click()
Dim sFormat As String

If TypeName(Selection) = "Range" Then
  sFormat = "#,##0"
  If lbDecimals.Text > "0" Then
    sFormat = sFormat + "." + String$(lbDecimals, "0")
  End If
  Selection.NumberFormat = sFormat
End If
Unload Me

End Sub

Private Sub pbCancel_Click()
 
 Unload Me
  
End Sub

Private Sub UserForm_Initialize()
Dim n As Integer
  For n = 0 To 6
    lbDecimals.AddItem (n)
  Next n
End Sub

====================================================

Add a module with the following code:

====================================================

Sub SetDecimalsForSelectedRange()

frmSelectDecimals.Show

End Sub

====================================================
Drop a button on the form and assign the macro.
Select a range (with numbers in it) then click the button.
Click the number of decimals you want.

This is set up for the simplest kind of decimal formatting. If you need something more exotic like accounting or red parentheses then the code will need revision.

(If you see a horizontal scroll bar at the bottom of the list box, set the ColumnWidths property to a small number, e.g. 6.)

Hope this helps.

 

[Zathras]

I should point out however, that there are already buttons on the tool bar to increase/decrease the number of decimals for the selected cells. It may be easier to train the users to use those. (And they handle all of the formats.)