Solved

Set Excel FixedDecimal on a worksheet

Posted on 2013-02-01
8
252 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
  • 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
 

Author Comment

by:rws1
ID: 38845985
then I would type 58700
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now