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

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

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

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