Solved

Set Excel FixedDecimal on a worksheet

Posted on 2013-02-01
8
261 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 47
DBF to ... Converter 5 40
FormulaArray VBA Issue 6 17
word is recognized as adobe and adobe as IE files 14 18
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

17 Experts available now in Live!

Get 1:1 Help Now