|
|
Cell value programming question |
|
Author |
Message |
GTB
|
Posted: Mon Sep 01 08:33:31 CDT 2003 |
Top |
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
Excel313
|
|
|
|
|
Bob
|
Posted: Mon Sep 01 08:33:31 CDT 2003 |
Top |
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
> 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
|
Posted: Mon Sep 01 08:56:42 CDT 2003 |
Top |
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
> 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
>
> > 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
|
Posted: Mon Sep 01 09:34:22 CDT 2003 |
Top |
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
> 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
>
> > 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
> >
> > > 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
> > >
> > >
> >
> >
>
>
|
|
|
|
|
|
|