Solved

Set Excel FixedDecimal on a worksheet

Posted on 2013-02-01
284 Views
Guys, what is the best way to set cells D7:D18, F7:F18 and E15:E17 on a worksheet name "hours worked" to FixedDecimal on that one worksheet only.
And then when I leave that worksheet for another worksheet in the workbook then the code would deactivate FixedDecimal to work on the other sheets.

Robert
0
Question by:rws1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4

LVL 43

Expert Comment

ID: 38845969
Select the cells
Press ctrl-1
Select the number tab
Select "Number"
Select the number of decimal places desired
Click Ok
0

Author Comment

ID: 38845979
ssaqibh, thank you for your response, however, I was hoping for a vba code that I would not have to enter the (.) when entering the number. For a result of 587.85, I would only enter 58785. Then when I left the sheet the fixed decimal would deactivate for the other pages in the workbook.
0

LVL 43

Expert Comment

ID: 38845984
What would you type if you wanted 587.00?
0

Author Comment

ID: 38845985
then I would type 58700
0

LVL 43

Expert Comment

ID: 38845989
Right-click on the sheet tab name
Select View code
Paste this code in the VBA window
Close the VBA window

``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D7:D18, F7:F18 ,E15:E17")) Is Nothing Then
If Target.Count > 1 Then
For Each cel In Target
Call Worksheet_Change(cel)
Next cel
Exit Sub
End If
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Target / 100
Application.EnableEvents = True
End If
End If
End Sub
``````
0

Author Comment

ID: 38846003
The formula works, thank you, however I when I click delete it does not delete, it turns all the cells to 00.00 and if I do click 587. then it does not recognize the the whole number, but makes it 5.87. Any work arounds for this would be appreciated,
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
ID: 38846025
This will take care of the Delete problem
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D7:D18, F7:F18 ,E15:E17")) Is Nothing Then
If Target.Count > 1 Then
For Each cel In Target
Call Worksheet_Change(cel)
Next cel
Exit Sub
End If
If IsNumeric(Target) And Not IsEmpty(Target) Then
Application.EnableEvents = False
Target = Target / 100
Application.EnableEvents = True
End If
End If
End Sub
``````
As far as clicking on 587 or any other number you will be facing the problem because excel considers as a new entry and repeats the process.
0

Author Comment

ID: 38846033
Thank you, works great
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Suggested Courses
Course of the Month4 days, 22 hours left to enroll