Solved

Set Excel FixedDecimal on a worksheet

Posted on 2013-02-01
8
284 Views
Last Modified: 2013-02-01
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.

Thank you for your help,
Robert
0
Comment
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
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

by:rws1
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

by:Saqib Husain, Syed
ID: 38845984
What would you type if you wanted 587.00?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:rws1
ID: 38845985
then I would type 58700
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

Open in new window

0
 

Author Comment

by:rws1
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

by:
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

Open in new window

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

by:rws1
ID: 38846033
Thank you, works great
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Leon
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…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question