Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Set Excel FixedDecimal on a worksheet

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
rws1
Asked:
rws1
  • 4
  • 4
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Select the cells
Press ctrl-1
Select the number tab
Select "Number"
Select the number of decimal places desired
Click Ok
0
 
rws1Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
What would you type if you wanted 587.00?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
rws1Author Commented:
then I would type 58700
0
 
Saqib Husain, SyedEngineerCommented:
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
 
rws1Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
rws1Author Commented:
Thank you, works great
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now