Select Case
 

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Select Case
 

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Select Case
 

Are you required to use VBA?
Will cell validation work instead?

See http://www.contextures.com/xlDataVal01.html

Thx
MSweetG222



"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Select Case
 

Jim, that is close to what I need. I'm sorry for the thin description. I have
a sheet that is used to schedule employees. Col A is for names, B is for
start time, C is for end time and D is for hours worked. When someone is
scheduled for vacation, sick time, or a holiday I want to be able to enter
the appropriate letter in column B and have it return 8 (as in hours) for
that employee.

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Select Case
 

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Select Case
 

Remember, you're still changing the cell to the right

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(Target, Me.Range("a1:a9,C3:c99,e14:e199,x5")) Is Nothing Then
Exit Sub
End If

Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select

End Sub


MikeG wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson

Select Case
 

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson


"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Select Case
 

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson

Select Case
 

Sure if you want the code to work you can do that but I thought that this was
much more inventive... ;-) Thanks for catching that one...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson

Select Case
 

The code works great except it removes a calculation for finding hours worked:
=IF(COUNT(B8:C8)=2,(C8-B8)*24-0.5,0)

Can the line after Case Else MsgBox "Not A, H, or V." be replaced with this
formula so that the calculation will take place if A, H, or V are not
selected?

Thanks!

"Jim Thomlinson" wrote:

Sure if you want the code to work you can do that but I thought that this was
much more inventive... ;-) Thanks for catching that one...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson

Select Case
 

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B2:B100")) Is Nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
Target.Offset(0, 2).FormulaR1C1 _
= "=IF(COUNT(RC[-2]:RC[-1])=2,(RC[-1]-RC[-2])*24-0.5,0)"
'MsgBox "Not A, H or V."
End Select
End If
End Sub


MikeG wrote:

The code works great except it removes a calculation for finding hours worked:
=IF(COUNT(B8:C8)=2,(C8-B8)*24-0.5,0)

Can the line after Case Else MsgBox "Not A, H, or V." be replaced with this
formula so that the calculation will take place if A, H, or V are not
selected?

Thanks!

"Jim Thomlinson" wrote:

Sure if you want the code to work you can do that but I thought that this was
much more inventive... ;-) Thanks for catching that one...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson


--

Dave Peterson

Select Case
 

That worked great! How about one more request. Right now, this works for
column B. What if I wanted it to aslo work for columns E, H, K, N, Q and T
for the rest of the week. Can this be done?

Thanks!

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B2:B100")) Is Nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
Target.Offset(0, 2).FormulaR1C1 _
= "=IF(COUNT(RC[-2]:RC[-1])=2,(RC[-1]-RC[-2])*24-0.5,0)"
'MsgBox "Not A, H or V."
End Select
End If
End Sub


MikeG wrote:

The code works great except it removes a calculation for finding hours worked:
=IF(COUNT(B8:C8)=2,(C8-B8)*24-0.5,0)

Can the line after Case Else MsgBox "Not A, H, or V." be replaced with this
formula so that the calculation will take place if A, H, or V are not
selected?

Thanks!

"Jim Thomlinson" wrote:

Sure if you want the code to work you can do that but I thought that this was
much more inventive... ;-) Thanks for catching that one...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson


--

Dave Peterson

Select Case
 

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub 'one cell at a time

If Intersect(Target, Me.Range("B2:B100,E2:E100,H2:H100,K2:K100," _
& "N2:N100,Q2:Q100,T2:T100")) Is Nothing Then
Exit Sub
End If

Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
Target.Offset(0, 2).FormulaR1C1 _
= "=IF(COUNT(RC[-2]:RC[-1])=2,(RC[-1]-RC[-2])*24-0.5,0)"
'MsgBox "Not A, H or V."
End Select

End Sub

I'm not sure what happens to the formula, though--is it still going in the cell
two to the right?

MikeG wrote:

That worked great! How about one more request. Right now, this works for
column B. What if I wanted it to aslo work for columns E, H, K, N, Q and T
for the rest of the week. Can this be done?

Thanks!

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B2:B100")) Is Nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
Target.Offset(0, 2).FormulaR1C1 _
= "=IF(COUNT(RC[-2]:RC[-1])=2,(RC[-1]-RC[-2])*24-0.5,0)"
'MsgBox "Not A, H or V."
End Select
End If
End Sub


MikeG wrote:

The code works great except it removes a calculation for finding hours worked:
=IF(COUNT(B8:C8)=2,(C8-B8)*24-0.5,0)

Can the line after Case Else MsgBox "Not A, H, or V." be replaced with this
formula so that the calculation will take place if A, H, or V are not
selected?

Thanks!

"Jim Thomlinson" wrote:

Sure if you want the code to work you can do that but I thought that this was
much more inventive... ;-) Thanks for catching that one...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Select Case
 

Yes Dave, it is still going to the right 2 places.

Thank you Dave and Jim for all your help. Not only have you answered my
problem, but you taught me too!

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub 'one cell at a time

If Intersect(Target, Me.Range("B2:B100,E2:E100,H2:H100,K2:K100," _
& "N2:N100,Q2:Q100,T2:T100")) Is Nothing Then
Exit Sub
End If

Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
Target.Offset(0, 2).FormulaR1C1 _
= "=IF(COUNT(RC[-2]:RC[-1])=2,(RC[-1]-RC[-2])*24-0.5,0)"
'MsgBox "Not A, H or V."
End Select

End Sub

I'm not sure what happens to the formula, though--is it still going in the cell
two to the right?

MikeG wrote:

That worked great! How about one more request. Right now, this works for
column B. What if I wanted it to aslo work for columns E, H, K, N, Q and T
for the rest of the week. Can this be done?

Thanks!

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B2:B100")) Is Nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
Target.Offset(0, 2).FormulaR1C1 _
= "=IF(COUNT(RC[-2]:RC[-1])=2,(RC[-1]-RC[-2])*24-0.5,0)"
'MsgBox "Not A, H or V."
End Select
End If
End Sub


MikeG wrote:

The code works great except it removes a calculation for finding hours worked:
=IF(COUNT(B8:C8)=2,(C8-B8)*24-0.5,0)

Can the line after Case Else MsgBox "Not A, H, or V." be replaced with this
formula so that the calculation will take place if A, H, or V are not
selected?

Thanks!

"Jim Thomlinson" wrote:

Sure if you want the code to work you can do that but I thought that this was
much more inventive... ;-) Thanks for catching that one...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson