PDA

View Full Version : Solved: Formatting of a cell


ukdane
02-20-2009, 12:53 AM
I have a workbook with two worksheets.
One worksheet is a "set up" page.
The other contains the data.
I have one field that the user can either input a number, or a %. Based on the option they make on the set up page.

How do I format the cell accordingly?

If Range("Setup!AP2") = 1 Then
Sheets("Data").Select
Range("F33").Value = FormatPercent("10%")
Else
Sheets("Data").Select
Range("F33").Value = Format("100")
End If

The above code looks to see if the data is set to be shown in % or as a number, and inputs a default value. However, I need to format the cell F33 so that if the user changes the default, it is shown as a % (or as a whole number).

How do I do this?
ukdane
02-20-2009, 01:31 AM
I should add, that if the user chooses %, and changes the default to (for example 75) then the field reads "75%". But if they choose a number, and change the default, it is adjusted to (for example 600) the field reads "600".

If I use Formatnumber("10%") it sets the default, but if I change it, it doesn't add the % to the end of the number, which I need it to.
Bob Phillips
02-20-2009, 03:11 AM
If Range("Setup!AP2") = 1 Then
Sheets("Data").Range("F33").NumberFormat= "0%"
Else
Sheets("Data").Range("F33").NumberFormat = "0"
End If
ukdane
02-20-2009, 03:26 AM
xld: That's fine in itself, but It doesn't show the % sign in the field F33 if the user changes the % from the default to something else.
alexgiurca
02-20-2009, 03:47 AM
I have found no other way to do it but this:
- when you have % in the field use VBA to concatenate the number with the percent and you get a list of numbers like 10%, 11%,12%,13% etc in sheet 'Data', column A;
- the problem is that you cannot change back the formatting to numbers. It always stays like "%" no matter what you do... Yeah right! :devil2: ;
- you make the transformation from sheet Data , column A, to string and remove the %, and have the data temporarily stored in sheet 1. Then you delete the entire Range with Range.delete . Afterwards you copy back the data from sheet 1 to sheet 'Data'.

See the attached file which has the complete solution to your problem.

Cheers mate!
:thumb
ukdane
02-20-2009, 04:24 AM
xld: I misread your vba... thanks it works.
Bob Phillips
02-20-2009, 04:43 AM
xld: That's fine in itself, but It doesn't show the % sign in the field F33 if the user changes the % from the default to something else.

I am not sure what you mean it is working fine for me.