round number for unknown num_digits

[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.

 

[Tom Ogilvy]

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

 

[Michael Malinsky]

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!

 

[Dave Peterson]

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.

 

[Michael Malinsky]

Well then I learned something...I didn't know that .numformat existed.

Thanks!