Cell value programming question

  • Thread starter Thread starter Anders Wåhlin
  • Start date Start date
A

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
 
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
 
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
 
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")
 
Back
Top