Whole vs decimal

[Phil Hageman]

In Excel 2000, I have created a worksheet for use on a
network. Certain cells (formatted percentage) have data
values that create an area chart. On my screen, I
enter "75" in the source cell: "75%" shows on screen, and
the chart plots a value at 75% (scale maximum is 100%).
However, other users have to enter ".75" to make things
work right.

Is there a way to have users enter a whole number, vs a
decimal?

Thanks, Phil

 

[Mike]

That is set in Tools/Options, go to the Edit tab and check
Fixed Decimals. Problem though, every time you enter a
whole number, it will move the decimal over two places
unless you add a decimal point. Your other users may not
like this because it is not the default way of entering
data. You may want to create workbook open and close
macros that toggle this setting.

 

[Stephen Bullen]

Hi Phil,

In Excel 2000, I have created a worksheet for use on a
network. Certain cells (formatted percentage) have data
values that create an area chart. On my screen, I
enter "75" in the source cell: "75%" shows on screen, and
the chart plots a value at 75% (scale maximum is 100%).
However, other users have to enter ".75" to make things
work right.

Is there a way to have users enter a whole number, vs a
decimal?

Tools > Options > Edit > Enable automatic percent entry

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Phil Hageman]

Stephen, Thanks for the quick reply. Could we modify the
following Autpen Sub to automatically make auto percent
entry setting?

Sub Autpen()

'This code puts the cursor in cell A1 in all worksheets,
'and opens the workbook on the "Scorecard" worksheet.
'It also operates when worksheets are hidden.

Application.ScreenUpdating = False

For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
ws.Select
Application.GoTo ws.Range("A1"), True
ActiveWindow.DisplayGridlines = False
End If
Next

Worksheets("Scorecard").Select

Application.ScreenUpdating = True

End Sub

 

[Phil Hageman]

Mike, Thanks for the quick reply. Could we modify the
following Autpen Sub to automatically make auto percent
entry setting?

Sub Autpen()

'This code puts the cursor in cell A1 in all worksheets,
'and opens the workbook on the "Scorecard" worksheet.
'It also operates when worksheets are hidden.

Application.ScreenUpdating = False

For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
ws.Select
Application.GoTo ws.Range("A1"), True
ActiveWindow.DisplayGridlines = False
End If
Next

Worksheets("Scorecard").Select

Application.ScreenUpdating = True

End Sub

 

[Bob Phillips]

Phil,

simply done

Sub Autpen()

'This code puts the cursor in cell A1 in all worksheets,
'and opens the workbook on the "Scorecard" worksheet.
'It also operates when worksheets are hidden.

Application.ScreenUpdating = False

For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then
ws.Select
Application.GoTo ws.Range("A1"), True
ActiveWindow.DisplayGridlines = False
End If
Next

Worksheets("Scorecard").Select

Application.AutoPercentEntry = True

Application.ScreenUpdating = True

End Sub

 

[Phil Hageman]

Bob, I entered the code and tried it out on a users
machine. The user still has to enter a decimal, but what
shows on screen in the cell is the percentage. Is this
the correct outcome for the added code? If so, okay -
I'll have to educate users.
Phil

 

[Tom Ogilvy]

Have the user enter 75%

it will be stored as 0.75 and displayed as 75%

or

If you preformat the range as %, then the user can just enter 75 and it will
be interpreted correctly.


Regards,
Tom Ogilvy

 

[Phil Hageman]

Once again, thanks Tom. Have done as you say. Will try
it out. Phil