round number for unknown num_digits

  • Thread starter Thread starter Michael Malinsky
  • Start date Start date
M

Michael Malinsky

I want to have a control on a UserForm (scollbar, slider, other
suggestions) in which the user can select the number of decimal places
(1 through 9) or significant digits (-1 through -9) for a range of
cells. The siginficant digits is easy:

ActiveCell.Value =
Format(WorksheetFunction.Round(ActiveCell.Value, Slider1.Value), _
"#,##0")

I'm having trouble coming up with a way to do the decimal places
portion of this code. The only solution I can come up with is to use a
Select...Case for each value 1 through 9 and have the appropriate
formatting under each, which seems like the lengthy way to do things,
but I'd like to see if there's a better way.

Thanks,
Mike.
 
Not sure you need the format function, but anyway:

if Slider1.Value > 0 then
s = "#,##0." & Application.Rept("0", Slider1.Value)
With ActiveCell
.NumberFormat = s
.Value = Format(WorksheetFunction.Round(ActiveCell.Value, Slider1.Value),
s)
End With
else
 
I really don't NEED the format function, but I like to do the
formatting via code.

Anyway...this appears to do the trick...Thanks!
 
I think that Tom's suggestion is that the line that does all the work is this
line: ".numberformat= s"

You could drop the Format() from the .value line.
 
Back
Top