?
Solved

Set Excel FixedDecimal on a worksheet

Posted on 2013-02-01
8
Medium Priority
?
288 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

719 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