Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value = "M" Then
userfcorm1.Show
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
Problem:

I am trying to reference cells on a worksheet that is below the main data
in the worksheet. When a cell with "M" in it is clicked, a userform appears
that should displays the hours in a txtbox that correspond to that date and
that person. The hours are referenced from the info below in rows 7-10.


Worksheet:
A B C D E
1 Bill Tom Tim Kim
2 7/1 M J F M
3 7/2 N F J S
4 7/3 F M S S
5
6
7 7/1 4 2
8 7/3 3
9
10


Please help!!! And give suggestions. thanx.
- CDotWin

It was me not reading it thoroughly enough. Try this version

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:E4")) Is Nothing Then
With Target
If .Value = "M" Then
With UserForm1
.textbox1.text = .Offset(5, 0).Value
.Show
End With
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
You are right, but........
I want a textbox in the userform to display the hours that correspond to
that date and person. I can get the userform to show, but I need the values
to show in the textbox as well.

i.e. - If I clicked the "M" in cell E2, then the textbox in the userform
should display the number "2". Or, if i clicked C4 then "3" should appear
in the textbox.

Sorry for the misunderstanding....
Thanx


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value = "M" Then
userfcorm1.Show
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
Problem:

I am trying to reference cells on a worksheet that is below the main
data
in the worksheet. When a cell with "M" in it is clicked, a userform
appears
that should displays the hours in a txtbox that correspond to that date
and
that person. The hours are referenced from the info below in rows 7-10.


Worksheet:
A B C D E
1 Bill Tom Tim Kim
2 7/1 M J F M
3 7/2 N F J S
4 7/3 F M S S
5
6
7 7/1 4 2
8 7/3 3
9
10


Please help!!! And give suggestions. thanx.
- CDotWin

It seems not to work properly.... Is there another way to do it without using the .Offset function because that produces an error?

"Bob Phillips" wrote:

It was me not reading it thoroughly enough. Try this version

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:E4")) Is Nothing Then
With Target
If .Value = "M" Then
With UserForm1
.textbox1.text = .Offset(5, 0).Value
.Show
End With
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
You are right, but........
I want a textbox in the userform to display the hours that correspond to
that date and person. I can get the userform to show, but I need the values
to show in the textbox as well.

i.e. - If I clicked the "M" in cell E2, then the textbox in the userform
should display the number "2". Or, if i clicked C4 then "3" should appear
in the textbox.

Sorry for the misunderstanding....
Thanx


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value = "M" Then
userfcorm1.Show
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
Problem:

I am trying to reference cells on a worksheet that is below the main
data
in the worksheet. When a cell with "M" in it is clicked, a userform
appears
that should displays the hours in a txtbox that correspond to that date
and
that person. The hours are referenced from the info below in rows 7-10.


Worksheet:
A B C D E
1 Bill Tom Tim Kim
2 7/1 M J F M
3 7/2 N F J S
4 7/3 F M S S
5
6
7 7/1 4 2
8 7/3 3
9
10


Please help!!! And give suggestions. thanx.
- CDotWin

Sorry, an error in the code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:E4")) Is Nothing Then
With Target
If .Value = "M" Then
UserForm1.textbox1.text = .Offset(5, 0).Value
Userform1.Show
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CDotWin" wrote in message
...
It seems not to work properly.... Is there another way to do it without
using the .Offset function because that produces an error?

"Bob Phillips" wrote:

It was me not reading it thoroughly enough. Try this version

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:E4")) Is Nothing Then
With Target
If .Value = "M" Then
With UserForm1
.textbox1.text = .Offset(5, 0).Value
.Show
End With
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
You are right, but........
I want a textbox in the userform to display the hours that correspond
to
that date and person. I can get the userform to show, but I need the
values
to show in the textbox as well.

i.e. - If I clicked the "M" in cell E2, then the textbox in the
userform
should display the number "2". Or, if i clicked C4 then "3" should
appear
in the textbox.

Sorry for the misunderstanding....
Thanx


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value = "M" Then
userfcorm1.Show
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
Problem:

I am trying to reference cells on a worksheet that is below the
main
data
in the worksheet. When a cell with "M" in it is clicked, a userform
appears
that should displays the hours in a txtbox that correspond to that
date
and
that person. The hours are referenced from the info below in rows
7-10.


Worksheet:
A B C D E
1 Bill Tom Tim Kim
2 7/1 M J F M
3 7/2 N F J S
4 7/3 F M S S
5
6
7 7/1 4 2
8 7/3 3
9
10


Please help!!! And give suggestions. thanx.
- CDotWin

It can't do that, it only shows the form if the value M is entered in A2:E4,
the code ignores anything else.

And I just tried it to make sure.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CDotWin" wrote in message
...
Thanks.... It works, but when I delete something anywhere on the sheet,
the userform1 appears. How do I prevent the userform from appearing when
something is deleted?

"Bob Phillips" wrote:

Sorry, an error in the code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:E4")) Is Nothing Then
With Target
If .Value = "M" Then
UserForm1.textbox1.text = .Offset(5, 0).Value
Userform1.Show
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CDotWin" wrote in message
...
It seems not to work properly.... Is there another way to do it
without
using the .Offset function because that produces an error?

"Bob Phillips" wrote:

It was me not reading it thoroughly enough. Try this version

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:E4")) Is Nothing Then
With Target
If .Value = "M" Then
With UserForm1
.textbox1.text = .Offset(5, 0).Value
.Show
End With
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
You are right, but........
I want a textbox in the userform to display the hours that
correspond
to
that date and person. I can get the userform to show, but I need
the
values
to show in the textbox as well.

i.e. - If I clicked the "M" in cell E2, then the textbox in the
userform
should display the number "2". Or, if i clicked C4 then "3" should
appear
in the textbox.

Sorry for the misunderstanding....
Thanx


"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If .Value = "M" Then
userfcorm1.Show
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CTInt04" wrote in message
...
Problem:

I am trying to reference cells on a worksheet that is below
the
main
data
in the worksheet. When a cell with "M" in it is clicked, a
userform
appears
that should displays the hours in a txtbox that correspond to
that
date
and
that person. The hours are referenced from the info below in
rows
7-10.


Worksheet:
A B C D E
1 Bill Tom Tim Kim
2 7/1 M J F M
3 7/2 N F J S
4 7/3 F M S S
5
6
7 7/1 4 2
8 7/3 3
9
10


Please help!!! And give suggestions. thanx.
- CDotWin