Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell value programming question
Hi!
In my Excel sheet I want to be able to enter two different values in a cell. The base value is percent (%) but if I enter the value in hours the value should be recalculated into percent (%). For example: 100% = 8 hours. If I enter 20, the cell should leave the value as is. If I enter 5h (note the 'h' character), the new value should be 62.5 since 5 hours is 62.5% of 8 hours. Do you understand my question? I use Excel 2000. Thanks! Regards /Anders |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell value programming question
Anders,
Here is some worksheet event code to do it. I have allowed for just cell A1, but you can change that to whatever cell(s) you want. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If (Not Intersect(Target, Range("A1")) Is Nothing) Then With Target If (LCase(Right(.Value, 1) = "h")) Then If IsNumeric(Left(.Value, Len(.Value) - 1)) Then .Value = Left(.Value, Len(.Value) - 1) / 8 .NumberFormat = "0.0%" Else MsgBox "Value is not numeric)" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Anders Wåhlin" wrote in message ... Hi! In my Excel sheet I want to be able to enter two different values in a cell. The base value is percent (%) but if I enter the value in hours the value should be recalculated into percent (%). For example: 100% = 8 hours. If I enter 20, the cell should leave the value as is. If I enter 5h (note the 'h' character), the new value should be 62.5 since 5 hours is 62.5% of 8 hours. Do you understand my question? I use Excel 2000. Thanks! Regards /Anders |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell value programming question
Thanks!
I'm not very good at Excel programming. How do I implement that code to my cells and make them handle it automatic? Regards /Anders "Bob Phillips" wrote in message ... Anders, Here is some worksheet event code to do it. I have allowed for just cell A1, but you can change that to whatever cell(s) you want. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If (Not Intersect(Target, Range("A1")) Is Nothing) Then With Target If (LCase(Right(.Value, 1) = "h")) Then If IsNumeric(Left(.Value, Len(.Value) - 1)) Then .Value = Left(.Value, Len(.Value) - 1) / 8 .NumberFormat = "0.0%" Else MsgBox "Value is not numeric)" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Anders Wåhlin" wrote in message ... Hi! In my Excel sheet I want to be able to enter two different values in a cell. The base value is percent (%) but if I enter the value in hours the value should be recalculated into percent (%). For example: 100% = 8 hours. If I enter 20, the cell should leave the value as is. If I enter 5h (note the 'h' character), the new value should be 62.5 since 5 hours is 62.5% of 8 hours. Do you understand my question? I use Excel 2000. Thanks! Regards /Anders |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell value programming question
Andres,
On the worksheet it applies to, right-click on the sheet name tab. From the menu, select View Code, and then paste the code into the code pane presented. It is worksheet event code, so it is automatic. Just change the 'Range("A1")' to the cells you want it to apply to, for instance Range("A1:A100"), or Range("A1,B1,C2,D9:D10") -- HTH Bob Phillips "Anders Wåhlin" wrote in message ... Thanks! I'm not very good at Excel programming. How do I implement that code to my cells and make them handle it automatic? Regards /Anders "Bob Phillips" wrote in message ... Anders, Here is some worksheet event code to do it. I have allowed for just cell A1, but you can change that to whatever cell(s) you want. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If (Not Intersect(Target, Range("A1")) Is Nothing) Then With Target If (LCase(Right(.Value, 1) = "h")) Then If IsNumeric(Left(.Value, Len(.Value) - 1)) Then .Value = Left(.Value, Len(.Value) - 1) / 8 .NumberFormat = "0.0%" Else MsgBox "Value is not numeric)" End If End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips "Anders Wåhlin" wrote in message ... Hi! In my Excel sheet I want to be able to enter two different values in a cell. The base value is percent (%) but if I enter the value in hours the value should be recalculated into percent (%). For example: 100% = 8 hours. If I enter 20, the cell should leave the value as is. If I enter 5h (note the 'h' character), the new value should be 62.5 since 5 hours is 62.5% of 8 hours. Do you understand my question? I use Excel 2000. Thanks! Regards /Anders |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell programming question | Excel Discussion (Misc queries) | |||
LOOKUP vs IF programming question | Excel Worksheet Functions | |||
Macro Programming Question | Excel Discussion (Misc queries) | |||
Programming Cell ID's | Excel Discussion (Misc queries) | |||
programming | Excel Discussion (Misc queries) |