Whole vs decimal

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

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
 
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.
 
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?
Click to expand...

Tools > Options > Edit > Enable automatic percent entry

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Stephen, Thanks for the quick reply. Could we modify the
following Auto_Open Sub to automatically make auto percent
entry setting?

Sub Auto_Open()

'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
 
Mike, Thanks for the quick reply. Could we modify the
following Auto_Open Sub to automatically make auto percent
entry setting?

Sub Auto_Open()

'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,

simply done

Sub Auto_Open()

'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
 
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
 
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
 
Back
Top