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), _

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.


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),
End With

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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question