Cell value programming question

[Anders Wåhlin]

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

 

[Bob Phillips]

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

 

[Anders Wåhlin]

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]

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,D910")