Standardize all cells at once

[asim]

Hello,

I would welcome some help with the following:

I would like the user to be able to perform the functions below, to all the numbers in a range a cells i.e. B3 to E13 (this will not change) by simply selecting an option or pressing a predefined button(s).
- Change all values to %
- Change all values to Number
- Change all values to Currency

Within each of these options they should be able to:
- Increase decimal place
- Decrease decimal place

I have attached a spreadsheet which this will be used on.

Can you help with a simple command/function or macro?

Regards
Asim

[JLatham]

You can do this yourself. Plan out each separate change you need to make (or
combination as changing to a number and setting number of decimal places to
display).

Use Tools | Macro | Record New Macro
and step through each process. Once you have completed one of the processes
- such as choosing a group of cells, changing them to % with some specific
number of decimals, and perhaps choosing another cell to unselect the first
bunch, then click the square "Stop Recording" button on the Record Macro tool
bar that should be floating around on your desktop; or use Tools | Macro |
Stop Recording to end it.

Repeat for each of the processes. You can assign a meaningful name to each
macro at the very beginning of the record new macro process. You can then
choose each macro from the Tools | Macro | Macros list in the workbook.

Since you say that it will always be the same group of cells, you shouldn't
have to change anything within the code generated.

By default, the code will be saved in the workbook where you create them.
But if you want those macros to always be avaliable to you in future
workbooks, you have a couple of choices:

Either base all new workbooks on the one you have created, or at the point
where you can give a name to the macro, you can also choose to save it in the
"Personal Macro Workbook" which would make them available all the time when
Excel is open on your machine.

"asim" wrote:

>
> Hello,
>
> I would welcome some help with the following:
>
> I would like the user to be able to perform the functions below, to all
> the numbers in a range a cells i.e. B3 to E13 (this will not change) by
> simply selecting an option or pressing a predefined button(s).
> - Change all values to %
> - Change all values to Number
> - Change all values to Currency
>
> Within each of these options they should be able to:
> - Increase decimal place
> - Decrease decimal place
>
> I have attached a spreadsheet which this will be used on.
>
> Can you help with a simple command/function or macro?
>
> Regards
> Asim
>
>
> +-------------------------------------------------------------------+
> |Filename: SLA_Forecast Tool.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4941 |
> +-------------------------------------------------------------------+
>
> --
> asim
> ------------------------------------------------------------------------
> asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
> View this thread: http://www.excelforum.com/showthread...hreadid=555694
>
>

[JLatham]

I should add, that if you want all of the changes in all of the cells to be
performed at one time, then don't record 2 or 3 or 4 separate macros. Just
start recording, record everything that needs to be done from start to
finish. Then when you choose that macro, it will all be done.

If you want a shortcut on the worksheet itself so that the user can click it
to make it run, I like using the Text Box from the View | Toolbars | Drawing
toolbar. Write whatever instructions you wish in it and make it look kind of
nice, move it to a convenient place on the worksheet. Then Right-click on it
and choose [Assign Macro] and choose the name of the macro you just recorded.
One-button do it all kind of thing.

"asim" wrote:

>
> Hello,
>
> I would welcome some help with the following:
>
> I would like the user to be able to perform the functions below, to all
> the numbers in a range a cells i.e. B3 to E13 (this will not change) by
> simply selecting an option or pressing a predefined button(s).
> - Change all values to %
> - Change all values to Number
> - Change all values to Currency
>
> Within each of these options they should be able to:
> - Increase decimal place
> - Decrease decimal place
>
> I have attached a spreadsheet which this will be used on.
>
> Can you help with a simple command/function or macro?
>
> Regards
> Asim
>
>
> +-------------------------------------------------------------------+
> |Filename: SLA_Forecast Tool.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4941 |
> +-------------------------------------------------------------------+
>
> --
> asim
> ------------------------------------------------------------------------
> asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
> View this thread: http://www.excelforum.com/showthread...hreadid=555694
>
>

[asim]

brilliant, your correct, thanks for your help.

[JLatham]

You're very welcome.

Part of my goal is to always try to make users a little more independent
than they were before an answer or other help was given. Hopefully this will
help you to become not only a better Excel user, but make you more
independent while using it, and so in the end you'll maybe even enjoy Excel
just a little bit more.

As for 'brilliant' - quite possibly a little over-stated, but I appreciate
it just the same :-)

"asim" wrote:

>
> brilliant, your correct, thanks for your help.
>
>
> --
> asim
> ------------------------------------------------------------------------
> asim's Profile: http://www.excelforum.com/member.php...o&userid=35530
> View this thread: http://www.excelforum.com/showthread...hreadid=555694
>
>