Sheet1 (Code) problem

D

DejaVu

I've received quite a bit of help from Ron Rosenfeld and Dave Peterso
on this project, but I've run into another snag. I get confused whe
it comes to private subs and passing variables back and forth. I'm no
sure whats up, but this code just stopped working all of a sudden..
say all of a sudden, but I'm sure that I've done something to it. Her
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 week
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 wit
it. I know that each section of this code works, because I had i
working at one point in time... If anyone can get this to work, i
would be greatly appreciated!!

Thanks

DejaV
 
B

Bob Phillips

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 said:
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
Click to expand...
 
D

DejaVu

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
 
B

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.

--

HTH

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

DejaVu

Bob said:
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.
Click to expand...

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 
D

DejaVu

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
 
B

Bob Phillips

DejaVu said:
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:
Click to expand...

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!!
Click to expand...

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

DejaVu

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
 
D

DejaVu

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
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Need to loop my macro 3
Runtime Error 1004 when runing Sub() from btnClick 2
Variable name question 3
Trying to run excel vba code when cell value changes from empty to entered value. 1
Call UDF by String Name 2
Excel Target.column for Data Validation would like to target some cells within column 1
Insert code 2

Top