Sheet1 (Code) problem

**DejaVu** · 06-21-2005, 03:41 PM

I've received quite a bit of help from Ron Rosenfeld and Dave Peterson on this project, but I've run into another snag. I get confused when it comes to private subs and passing variables back and forth. I'm not sure whats up, but this code just stopped working all of a sudden.. I say all of a sudden, but I'm sure that I've done something to it. Here is all my code in the worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'** This portion used for the sales manager initials.
' Used to make sure that three letters are used.

Dim AOI As Range
Dim str As String

Set AOI = [C4]
Application.EnableEvents = False
If Not Intersect(Target, AOI) Is Nothing Then
str = UCase(Target.Text)
If str Like "[A-Z][A-Z][A-Z]" Then
Target.Value = str
GoTo NormalExit
Else
GoTo ErrorExit
End If
Else
GoTo NormalExit
End If

ErrorExit:
MsgBox "Only three letter entries allowed.", vbExclamation
Application.Undo
Target.ClearContents
NormalExit: Application.EnableEvents = True
End Sub

'*******************************************************************

Private Sub Worksheet_Change2(ByVal Target As Range)
'** This section is used to make sure that cells are not cleared
' with the space bar. This is to enforce accurate totals.

Dim myCell As Range
On Error Resume Next
Application.EnableEvents = False
For Each myCell In Target.Cells
If myCell.HasFormula Then
'do nothing
Else
If Trim(myCell.Value) = "" Then
myCell.Value = ""
End If
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0
End Sub

'*******************************************************************

Private Sub Worksheet_Change3(ByVal Target As Range)
'** Make sure they enter PC or NC in column B for Code.

Dim CaseRng As Range
On Error Resume Next
Application.EnableEvents = False
For Each CaseRng In Target.Cells
If CaseRng.Column = 2 Then
If CaseRng.Value = LCase(Target.Value) Then
Target.Value = UCase(Target.Value)
Else
'do nothing
End If
End If
Next CaseRng
Application.EnableEvents = True
On Error GoTo 0
End Sub

'*******************************************************************
'Private Sub Worksheet_Change4()
'** This section is used to make sure the date entered is a Saturday
' If it is not a Saturday, it will adjust the date to that weeks Saturday.

'Dim r As Range
'Dim idate As Date
'Dim iday As String
'Application.EnableEvents = False
'Set r = Range("K4")
'
'If r.Value = "" Then
' End
'End If
'
'idate = r.Value
'iday = Format(idate, "dddd")
'
'Select Case iday
' Case Is = "Sunday"
' idate = idate + 6
' Case Is = "Monday"
' idate = idate + 5
' Case Is = "Tuesday"
' idate = idate + 4
' Case Is = "Wednesday"
' idate = idate + 3
' Case Is = "Thursday"
' idate = idate + 2
' Case Is = "Friday"
' idate = idate + 1
' Case Is = "Saturday"
'End Select
'r.Value = idate
'Application.EnableEvents = True
'
'End Sub

Some of it is commented out because I was trying different things with it. I know that each section of this code works, because I had it working at one point in time... If anyone can get this to work, it would be greatly appreciated!!

Thanks

DejaVu

Bob Phillips · 06-21-2005, 06:05 PM

There is only one Worksheet_Change event procedure with any one sheet. The
....2, ...3 and ...4 versions never get invoked as far as I can see.

Describe what you are trying to do, in narrative form, what works and what
doesn't.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> I've received quite a bit of help from Ron Rosenfeld and Dave Peterson
> on this project, but I've run into another snag. I get confused when
> it comes to private subs and passing variables back and forth. I'm not
> sure whats up, but this code just stopped working all of a sudden.. I
> say all of a sudden, but I'm sure that I've done something to it. Here
> is all my code in the worksheet:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> '** This portion used for the sales manager initials.
> ' Used to make sure that three letters are used.
>
> Dim AOI As Range
> Dim str As String
>
> Set AOI = [C4]
> Application.EnableEvents = False
> If Not Intersect(Target, AOI) Is Nothing Then
> str = UCase(Target.Text)
> If str Like "[A-Z][A-Z][A-Z]" Then
> Target.Value = str
> GoTo NormalExit
> Else
> GoTo ErrorExit
> End If
> Else
> GoTo NormalExit
> End If
>
> ErrorExit:
> MsgBox "Only three letter entries allowed.", vbExclamation
> Application.Undo
> Target.ClearContents
> NormalExit: Application.EnableEvents = True
> End Sub
>
> '*******************************************************************
>
> Private Sub Worksheet_Change2(ByVal Target As Range)
> '** This section is used to make sure that cells are not cleared
> ' with the space bar. This is to enforce accurate totals.
>
> Dim myCell As Range
> On Error Resume Next
> Application.EnableEvents = False
> For Each myCell In Target.Cells
> If myCell.HasFormula Then
> 'do nothing
> Else
> If Trim(myCell.Value) = "" Then
> myCell.Value = ""
> End If
> End If
> Next myCell
> Application.EnableEvents = True
> On Error GoTo 0
> End Sub
>
> '*******************************************************************
>
> Private Sub Worksheet_Change3(ByVal Target As Range)
> '** Make sure they enter PC or NC in column B for Code.
>
> Dim CaseRng As Range
> On Error Resume Next
> Application.EnableEvents = False
> For Each CaseRng In Target.Cells
> If CaseRng.Column = 2 Then
> If CaseRng.Value = LCase(Target.Value) Then
> Target.Value = UCase(Target.Value)
> Else
> 'do nothing
> End If
> End If
> Next CaseRng
> Application.EnableEvents = True
> On Error GoTo 0
> End Sub
>
> '*******************************************************************
> 'Private Sub Worksheet_Change4()
> '** This section is used to make sure the date entered is a Saturday
> ' If it is not a Saturday, it will adjust the date to that weeks
> Saturday.
>
> 'Dim r As Range
> 'Dim idate As Date
> 'Dim iday As String
> 'Application.EnableEvents = False
> 'Set r = Range("K4")
> '
> 'If r.Value = "" Then
> ' End
> 'End If
> '
> 'idate = r.Value
> 'iday = Format(idate, "dddd")
> '
> 'Select Case iday
> ' Case Is = "Sunday"
> ' idate = idate + 6
> ' Case Is = "Monday"
> ' idate = idate + 5
> ' Case Is = "Tuesday"
> ' idate = idate + 4
> ' Case Is = "Wednesday"
> ' idate = idate + 3
> ' Case Is = "Thursday"
> ' idate = idate + 2
> ' Case Is = "Friday"
> ' idate = idate + 1
> ' Case Is = "Saturday"
> 'End Select
> 'r.Value = idate
> 'Application.EnableEvents = True
> '
> 'End Sub
>
>
>
> Some of it is commented out because I was trying different things with
> it. I know that each section of this code works, because I had it
> working at one point in time... If anyone can get this to work, it
> would be greatly appreciated!!
>
> Thanks
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=380999
>

**DejaVu** · 06-22-2005, 09:28 AM

Bob -
Each section (broken up by '********...etc...) does a different function for me.
The first part makes sure that the sales initials are entered as three uppercase letters.
The Second part makes sure that no cells are cleared out by using the space bar.
The third part tries to make sure that the only thing they can enter into column B is 'PC' or 'NC'.
Finally the forth part makes sure that the date they enter is a week ending Saturday.

I'd like to restructure this so that they are not broken up, and all in the same Sub. I had help creating each one of these, so I'm not sure how to put them together.

Bob, I'm not sure what I've done to it, but it was "working". By that I mean that it was doing the things that I previously described. It was (1) making sure the sales initials were entered as three uppercase letters, etc....

Now, there is no validation at all...

TIA for your help Bob!!

DejaVu

Bob Phillips · 06-22-2005, 03:05 PM

As it stands, only the first can ever run.

Would I be correct in assuming that they are all OR conditions, that is they
will enter something to upshift (in C4), OR the cells cleared bit (in range
??), OR PC or NC (in range ??) OR date check (in range ??). If so, we can
easily amalgamate.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> Bob -
> Each section (broken up by '********...etc...) does a different
> function for me.
> The first part makes sure that the sales initials are entered as three
> uppercase letters.
> The Second part makes sure that no cells are cleared out by using the
> space bar.
> The third part tries to make sure that the only thing they can enter
> into column B is 'PC' or 'NC'.
> Finally the forth part makes sure that the date they enter is a week
> ending Saturday.
>
> I'd like to restructure this so that they are not broken up, and all in
> the same Sub. I had help creating each one of these, so I'm not sure
> how to put them together.
>
> Bob, I'm not sure what I've done to it, but it was "working". By that
> I mean that it was doing the things that I previously described. It
> was (1) making sure the sales initials were entered as three uppercase
> letters, etc....
>
> Now, there is no validation at all...
>
> TIA for your help Bob!!
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=380999
>

**DejaVu** · 06-23-2005, 09:13 AM

Originally Posted by Bob Phillips

As it stands, only the first can ever run.

Would I be correct in assuming that they are all OR conditions, that is they
will enter something to upshift (in C4), OR the cells cleared bit (in range
??), OR PC or NC (in range ??) OR date check (in range ??). If so, we can easily amalgamate.

No, they are not OR conditions.. they are more of AND conditions. I need each of them to be true. I only want three letters entered into cell C4, week ending Saturday in cell K4, etc...

I need each of these sections to be true... I doubt it is this simple, but can I just put them all into one Private Sub? I tried this, but nothing worked... again...

Thanks for your help on all this Bob!!

DejaVu

Bob Phillips · 06-23-2005, 12:05 PM

That might be easier.

It might take a couple of iterations, but let's go for it.

Goive this a good thorugh testing, and report back pleasea.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'** This portion used for the sales manager initials.
' Used to make sure that three letters are used.

Dim AOI As Range
Dim str As String

Set AOI = [C4]
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, AOI) Is Nothing Then
str = UCase(Target.Text)
If str Like "[A-Z][A-Z][A-Z]" Then
Target.Value = str
Else
GoTo ErrorExit
End If
End If

'** This section is used to make sure that cells are not cleared
' with the space bar. This is to enforce accurate totals.

Dim myCell As Range

For Each myCell In Target.Cells
If myCell.HasFormula Then
'do nothing
Else
If Trim(myCell.Value) = "" Then
myCell.Value = ""
End If
End If
Next myCell

'** Make sure they enter PC or NC in column B for Code.

Dim CaseRng As Range

For Each CaseRng In Target.Cells
If CaseRng.Column = 2 Then
If CaseRng.Value = LCase(Target.Value) Then
Target.Value = UCase(Target.Value)
Else
'do nothing
End If
End If
Next CaseRng
Application.EnableEvents = True
On Error GoTo 0

Dim r As Range
Dim idate As Date
Dim iday As String

Application.EnableEvents = False
Set r = Range("K4")

If r.Value <> "" Then
idate = r.Value
iday = Format(idate, "dddd")

Select Case iday
Case Is = "Sunday"
idate = idate + 6
Case Is = "Monday"
idate = idate + 5
Case Is = "Tuesday"
idate = idate + 4
Case Is = "Wednesday"
idate = idate + 3
Case Is = "Thursday"
idate = idate + 2
Case Is = "Friday"
idate = idate + 1
Case Is = "Saturday"
End Select
r.Value = idate
End If

ErrorExit:
MsgBox "Only three letter entries allowed.", vbExclamation
Application.Undo
Target.ClearContents

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> I've received quite a bit of help from Ron Rosenfeld and Dave Peterson
> on this project, but I've run into another snag. I get confused when
> it comes to private subs and passing variables back and forth. I'm not
> sure whats up, but this code just stopped working all of a sudden.. I
> say all of a sudden, but I'm sure that I've done something to it. Here
> is all my code in the worksheet:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> '** This portion used for the sales manager initials.
> ' Used to make sure that three letters are used.
>
> Dim AOI As Range
> Dim str As String
>
> Set AOI = [C4]
> Application.EnableEvents = False
> If Not Intersect(Target, AOI) Is Nothing Then
> str = UCase(Target.Text)
> If str Like "[A-Z][A-Z][A-Z]" Then
> Target.Value = str
> GoTo NormalExit
> Else
> GoTo ErrorExit
> End If
> Else
> GoTo NormalExit
> End If
>
> ErrorExit:
> MsgBox "Only three letter entries allowed.", vbExclamation
> Application.Undo
> Target.ClearContents
> NormalExit: Application.EnableEvents = True
> End Sub
>
> '*******************************************************************
>
> Private Sub Worksheet_Change2(ByVal Target As Range)
> '** This section is used to make sure that cells are not cleared
> ' with the space bar. This is to enforce accurate totals.
>
> Dim myCell As Range
> On Error Resume Next
> Application.EnableEvents = False
> For Each myCell In Target.Cells
> If myCell.HasFormula Then
> 'do nothing
> Else
> If Trim(myCell.Value) = "" Then
> myCell.Value = ""
> End If
> End If
> Next myCell
> Application.EnableEvents = True
> On Error GoTo 0
> End Sub
>
> '*******************************************************************
>
> Private Sub Worksheet_Change3(ByVal Target As Range)
> '** Make sure they enter PC or NC in column B for Code.
>
> Dim CaseRng As Range
> On Error Resume Next
> Application.EnableEvents = False
> For Each CaseRng In Target.Cells
> If CaseRng.Column = 2 Then
> If CaseRng.Value = LCase(Target.Value) Then
> Target.Value = UCase(Target.Value)
> Else
> 'do nothing
> End If
> End If
> Next CaseRng
> Application.EnableEvents = True
> On Error GoTo 0
> End Sub
>
> '*******************************************************************
> 'Private Sub Worksheet_Change4()
> '** This section is used to make sure the date entered is a Saturday
> ' If it is not a Saturday, it will adjust the date to that weeks
> Saturday.
>
> 'Dim r As Range
> 'Dim idate As Date
> 'Dim iday As String
> 'Application.EnableEvents = False
> 'Set r = Range("K4")
> '
> 'If r.Value = "" Then
> ' End
> 'End If
> '
> 'idate = r.Value
> 'iday = Format(idate, "dddd")
> '
> 'Select Case iday
> ' Case Is = "Sunday"
> ' idate = idate + 6
> ' Case Is = "Monday"
> ' idate = idate + 5
> ' Case Is = "Tuesday"
> ' idate = idate + 4
> ' Case Is = "Wednesday"
> ' idate = idate + 3
> ' Case Is = "Thursday"
> ' idate = idate + 2
> ' Case Is = "Friday"
> ' idate = idate + 1
> ' Case Is = "Saturday"
> 'End Select
> 'r.Value = idate
> 'Application.EnableEvents = True
> '
> 'End Sub
>
>
>
> Some of it is commented out because I was trying different things with
> it. I know that each section of this code works, because I had it
> working at one point in time... If anyone can get this to work, it
> would be greatly appreciated!!
>
> Thanks
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=380999
>

**DejaVu** · 06-23-2005, 02:03 PM

Bob -
I copied the code into the sheet module. When I went back to the sheet and tried to type the three initials, (I typed SSS) it came up with the warning message, then jumped to the code:

ErrorExit:
MsgBox "Only three letter entries allowed.", vbExclamation
Application.Undo
Target.ClearContents

When I chose to 'End', it seems that nothing works anymore. So I cannot test the rest of it.

Thanks again for all your help!!

DejaVu

Bob Phillips · 06-23-2005, 05:05 PM

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> Bob -
> I copied the code into the sheet module. When I went back to the sheet
> and tried to type the three initials, (I typed SSS) it came up with the
> warning message, then jumped to the code:

Okay Version 2

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'** This portion used for the sales manager initials.
' Used to make sure that three letters are used.

Dim AOI As Range
Dim str As String

Set AOI = [C4]
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, AOI) Is Nothing Then
str = UCase(Target.Text)
If str Like "[A-Z][A-Z][A-Z]" Then
Target.Value = str
Else
GoTo ErrorExit
End If
End If

'** This section is used to make sure that cells are not cleared
' with the space bar. This is to enforce accurate totals.

Dim myCell As Range

For Each myCell In Target.Cells
If myCell.HasFormula Then
'do nothing
Else
If Trim(myCell.Value) = "" Then
myCell.Value = ""
End If
End If
Next myCell

'** Make sure they enter PC or NC in column B for Code.

Dim CaseRng As Range

For Each CaseRng In Target.Cells
If CaseRng.Column = 2 Then
If CaseRng.Value = LCase(Target.Value) Then
Target.Value = UCase(Target.Value)
Else
'do nothing
End If
End If
Next CaseRng

Dim r As Range
Dim idate As Date
Dim iday As String

Set r = Range("K4")

If r.Value <> "" Then
idate = r.Value
iday = Format(idate, "dddd")

Select Case iday
Case Is = "Sunday"
idate = idate + 6
Case Is = "Monday"
idate = idate + 5
Case Is = "Tuesday"
idate = idate + 4
Case Is = "Wednesday"
idate = idate + 3
Case Is = "Thursday"
idate = idate + 2
Case Is = "Friday"
idate = idate + 1
Case Is = "Saturday"
End Select
r.Value = idate
End If

GoTo ws_exit

ErrorExit:
MsgBox "Only three letter entries allowed.", vbExclamation
Application.Undo
Target.ClearContents

ws_exit:
Application.EnableEvents = True
End Sub

> Thanks again for all your help!!

Can you please stop saying that - I believe you :-)

**DejaVu** · 06-24-2005, 09:39 AM

That one worked fine for what I was wanting. Thanks. I have one more question though:
Every time I type something into my worksheet, it will not let me hit the Undo button. I imagine it has to do with the code (especially the code that clears cells that are cleared with a space). Is there any way that I can still use the undo button?

Tha..... nevermind.. you know!!

DejaVu

Bob Phillips · 06-24-2005, 03:05 PM

Don't think so.

Most VBA commands clear the undo buffer AFAIK.

Bob

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> That one worked fine for what I was wanting. Thanks. I have one more
> question though:
> Every time I type something into my worksheet, it will not let me hit
> the Undo button. I imagine it has to do with the code (especially the
> code that clears cells that are cleared with a space). Is there any
> way that I can still use the undo button?
>
>
> Tha..... nevermind.. you know!!
>
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=380999
>

**DejaVu** · 06-24-2005, 04:07 PM

Last question on this Bob, I promise (maybe...)

Can I do something like what we did for the sales initials (3 letters all caps) to the entire column B? Except one difference, I want them to be only able to enter "PC" or "NC".

I tried to modify this:
If str Like "[A-Z][A-Z][A-Z]" Then
into something like this:
If str Like "PC" Or "NC" Then

I couldnt get this to work. Do you have any ideas?

DejaVu

Bob Phillips · 06-24-2005, 08:05 PM

In this case, Like is not necessary, you can be specific

If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Ucase(Target.Value) = "PC" OR _
UCase(Target.Value) = "NC" Then
'... do your stuff
End If
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)

"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> Last question on this Bob, I promise (maybe...)
>
> Can I do something like what we did for the sales initials (3 letters
> all caps) to the entire column B? Except one difference, I want them
> to be only able to enter "PC" or "NC".
>
> I tried to modify this:
> If str Like "[A-Z][A-Z][A-Z]" Then
> into something like this:
> If str Like "PC" Or "NC" Then
>
> I couldnt get this to work. Do you have any ideas?
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=380999
>