Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

Excel 2007, Text Valiation HELP!

Thank You all for helping me!

What I want to happen is:
1.) User Selects from drop-down in ColC
     a.) If match in ColB then no mandatory enter in ColF
          1.) If ColC =  "N/A" Or "FOUO" Or "FRD" OR "Ref" Then ColD and ColE = "N/A and is protected
          2.) If User changes value to anything else then ColD and ColE are blank and unprotected
     b.) If no match in ColB then mandatory enter in ColF
           1.) If ColC  = "N/A" Or "FOUO" Or "FRD" OR "Ref" Then ColD and ColE = "N/A and is protected
            2.) If User changes value to anything else then ColD and ColE are blank and unprotected
     c.) If user selected a value that was mandatory entry then: ColF would have:
ColC: this is mandatory entry
      d.) if user changes the mandatory entry in ColC then ColF is blank but only that protion of the cell is blank the rest of the data stays and is protected
2.) User Selects from drop-down in ColD
      a.) if mandatory enter then: ColF would have:
ColD: this is mandatory entry
     b.) if user changes the mandatory entry in ColD then ColF is blank but only that protion of the cell is blank the rest of the data stays and is protected
3.) User selects from drop-down in ColE
    a.) user selects N/A then ColF would have:
ColE: this is mandatory entry
    b.) user changes the N/A entry in ColE then ColF is blank but only that protion of the cell is blank the rest of the data stays and is protected


Here is code that I have right now:


 
And This:

 

Also see attached to give you some idea.



CDEtoF.xls



Please help me and thank you!
Avatar of Curt Lindstrom
Curt Lindstrom
Flag of Australia image

Amour22015,

You already have one question in progress

Q_27530046.html#a37417924

 with exactly the same problem. You should include all explanations in that question instead of creating a new question.

Cheers,
Curt
Avatar of Amour22015
Amour22015

ASKER

epaclm

Thank you for helping me:

I only asked a smaller portion, then realized that I needed to repost the whole thing.  but If you help with one then you get both.  I think this problem is worth 2 x 500 because of the compatitcity of the Sub routines.


Thank You for helping me!
Thank you all for helping me!

I have done some test on CDE to F

If I just do the Copy and SaveAs (this is done in Access) then I open the Workbook everything is fine and it opens correctly.

If I don't save the copied workbook and reopen everything is fine.

If I do some work in the Workbook (not including CDEtoF) and save it then everything is fine.

If I work on the CDEtoF section of the workbook everything is NOT FINE and I receive the error:
Run-time error '-2147467259(80004005)': Method 'CopyFromRecordset' of object 'Range' failed

I have had this message before and have had to restore back to a copy of the workbook.

But this time was my first time testing CDEtoF with a save to the workbook.

I hope that someone can help me with all this?


Please help me

Thank you all
Avatar of Chris Raisin
Stand By...I am looking into this and will have answer ASAP....Cheers, Chris
Everything is fine if you just open the workbook and don't change anything in the range CDEtoF because the code does not do anything until somethig is changed
in that region of the spreadsheet.

(The test in the line:
      If Not Intersect(Target, Range("C1:E600")) Is Nothing Then
  equates as false if the target is not in the range C1 to E600. )

In your code for "Worksheet_Change" you issued the command "GoTo BeforeExit"
but that label does not exist.

I assume you want the label placed just before the final "EndIf" ?

I will work withat assumption in mind....

Stand by...


Cheers...Chris (Australia)
The first time I ran the code, an error occurred as you described, but I cannot get it to repeat!

The macro does not appear to work after it's initial run for some reason.

Is the worksheet supposed to be protected? (It currently is and so n data can be entered in anything outside the range C1 to E600 without a protection message popping up.

Also, what is the funtion of the button "Hide Headers"? It does not appear to do anything and I cannot see any code behind it.

I await your reponses...

Cheers
Chris

Finally, exactly what is the purpose of the code?
Is it supposed to trap certain conditions when the user types something in manually rather than via a "pull-down"??

Cheers
Chris
I think I have found something...you turn off events in your code via "Application.EnableEvents = False" and never seem to turn it back on again.
That means the events will not be "sensed" until you re-run your application (re-load the spreadsheet).

Stand by...this may be the fix we need.

Cheers
Chris
Sorry, I just noticed you turn events back on again, but of course, when an error happens while "events" have been turned off, they fon't come on again.

Stand by...
Hate the spell checker not being on any more!....

I'll repeat that:

Sorry, I just noticed you turn events back on again, but of course, when an error happens while "events" have been turned off, they don't come on again.

Stand by...
There appears there should be some data in the location:
                 
                HeaderCDE = Cells(600, Change.Column)
Can you please explain exactly what the subroutine "EditCDEinF"
is supposed to do?

I await your response.

(Moderator: Is the author seeing these comments?)

Forget that last question...I can see what is happening...stand by

Cheers
Chris
No, I come back to the same question.

What is the purpose of the line:  
     HeaderCDE = Cells(600, Change.Column)
There does not appear to be any data at that location!

Please advise....
craisin

Thank you for helping me!

HeaderCDE = Cells(600, change.column)

There is data in the main Workbook in that location

That workbook that I gave as an example is:
HeaderCDE = Cells(1, change.column)

just in the main Workbook changed the location to Row 600

Thank you for helping!
craisin

Thank you for helping me!

In your code for "Worksheet_Change" you issued the command "GoTo BeforeExit"
but that label does not exist.
I assume you want the label placed just before the final "EndIf" ?

I will work withat assumption in mind....


yes that would be correct.
BeforeExit:
   Application.EnableEvents = True
End Sub



Here is code that I have right now:


 

And This:

 


Also see attached to give you some idea.




It appears some of my orginal posting is missing.  So I going to copy the code again.  As for the workbook:
CDEtoF.xls
that is only an example and is not exact.

So here is the code:

 
Option Explicit
Dim TargetIsEmpty As Boolean
Private Sub Worksheet_Activate()
TargetIsEmpty = True

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
EditCDEinF Target
Cancel = True
End Sub

Open in new window




And this:

 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim SaveValC As String
Dim SaveVal1 As String
Dim fd As FileDialog
Dim link As Range
Dim item As Variant
Dim Obj As Object
Dim IsColUpdated As Boolean

    If Not Intersect(Target, Range("C1:E600")) Is Nothing Then
    
         'Selected with Other option
        If Cells(Target.Row, 3) = "Other" Then
             'require data entry in Col C
            Application.EnableEvents = False
            'Do While Cells(Target.Row, 3) = ""
                 'Get data from user
                If SaveValC <> "" Then
                    Cells(Target.Row, 3) = InputBox("Enter data for CLASSIFICATION", "Mandatory data entry", SaveValC)
                Else
                    Cells(Target.Row, 3) = InputBox("Enter data for CLASSIFICATION", "Mandatory data entry")
                End If
                 SaveValC = Cells(Target.Row, 3)
            'Loop
            Application.EnableEvents = True
        End If
' If ColE equal anything get out
   'If Cells(Target.Row, 5) = "N/A" Or Cells(Target.Row, 5) = "1.4(a)" Or Cells(Target.Row, 5) = "1.4(b)" Or Cells(Target.Row, 5) = "1.4(c)" Or Cells(Target.Row, 5) = "1.4(d)" Or Cells(Target.Row, 5) = "1.4(e)" Or Cells(Target.Row, 5) = "1.4(f)" Or Cells(Target.Row, 5) = "1.4(g)" Or Cells(Target.Row, 5) = "1.4(h)" Then
   'GoTo BeforeExit
   'End If

'If Classification Level = DoN Baseline Classification exit
    If Cells(Target.Row, 3) = Cells(Target.Row, 2) Then
' Check If ColC N/A, FRD, FOUO, NATO-U, NATO-RD, Ref
    If Cells(Target.Row, 3) = "N/A" Or Cells(Target.Row, 3) = "FRD" Or Cells(Target.Row, 3) = "FOUO" Or Cells(Target.Row, 3) = "NATO-U" Or Cells(Target.Row, 3) = "NATO-RD" Or Cells(Target.Row, 3) = "Ref" Then
    Application.EnableEvents = False
    Cells(Target.Row, 4) = "N/A"
    Cells(Target.Row, 5) = "N/A"
    Application.EnableEvents = True
    Else
    If Cells(Target.Row, 4) = "N/A" And Cells(Target.Row, 5) = "N/A" Then
    Application.EnableEvents = False
    Cells(Target.Row, 4) = " "
    Cells(Target.Row, 5) = " "
    Application.EnableEvents = True
    End If
    End If
    GoTo BeforeExit
    End If
    
'Check If ColC N/A, FRD, FOUO, NATO-U, NATO-RD, Ref
    If Cells(Target.Row, 3) = "N/A" Or Cells(Target.Row, 3) = "FRD" Or Cells(Target.Row, 3) = "FOUO" Or Cells(Target.Row, 3) = "NATO-U" Or Cells(Target.Row, 3) = "NATO-RD" Or Cells(Target.Row, 3) = "Ref" Then
    ActiveSheet.Unprotect
    Application.EnableEvents = False
    Cells(Target.Row, 4) = "N/A"
    Cells(Target.Row, 5) = "N/A"
    Application.EnableEvents = True
    ActiveSheet.Protect
    Else
    If Cells(Target.Row, 4) = "N/A" And Cells(Target.Row, 5) = "N/A" Then
    Application.EnableEvents = False
    Cells(Target.Row, 4) = " "
    Cells(Target.Row, 5) = " "
    Application.EnableEvents = True
    End If
    End If
    
'If Declass Date <> Other Exit
    If Cells(Target.Row, 4) = 5 Or Cells(Target.Row, 4) = 10 Or Cells(Target.Row, 4) = 15 Or Cells(Target.Row, 4) = 20 Or Cells(Target.Row, 4) = 25 Then
    GoTo BeforeExit
    End If
        
        'Check to see if the item selected has already been saved
    IsColUpdated = False
    If InStr(1, Cells(Target.Row, "F"), Cells(1, Target.Column)) <> 0 Then
        IsColUpdated = True
    End If

    If Not TargetIsEmpty Or IsColUpdated Then
        EditCDEinF Target
    Else
        CDEtoF Target
    End If
    End If

BeforeExit:
   Application.EnableEvents = True
End Sub

Open in new window


And This:

 
Sub CDEtoF(Target As Range)
Dim Change As Range
Dim Message As String
Dim MaxRow As Long

If Not Intersect(Target, Range("C1:E600")) Is Nothing Then
        For Each Change In Target.Cells
            If Change.Text <> "N/A" And Target.Text <> "" Then
                Application.EnableEvents = False
                ActiveSheet.Unprotect
                'Get data from user
                Do While Message = ""
                 Message = InputBox("Enter Justification for Column [" & Cells(600, Change.Column) & "]", "Mandatory data entry")
                Loop
                '-- insert data in target sheet
                MaxRow = Change.Row
                
                '-- insert comment in current sheet
                If Range("F1") <> "" Then MaxRow = MaxRow
                If Cells(MaxRow, 6) <> "" Then Cells(MaxRow, 6) = Cells(MaxRow, 6) & Chr(10) & Chr(10)
                Cells(MaxRow, 6) = Cells(MaxRow, 6) & Cells(600, Change.Column) & ": " & Message
                ActiveSheet.Protect AllowFiltering:=True
                Application.EnableEvents = True
            End If
        Next Change
    End If

End Sub

Sub EditCDEinF(Target As Range)
Dim Change As Range
Dim Message As String, MessageNEW As String, HeaderCDE As String
Dim MaxRow As Long
Dim StartMSG As Integer, EndMSG As Integer
Dim CellD As String

If Not Intersect(Target, Range("C1:E600")) Is Nothing Then
        For Each Change In Target.Cells
            If Change.Text <> "N/A" And Target.Text <> "" Then
                Application.EnableEvents = False
                ActiveSheet.Unprotect
                'Get data from D
                MaxRow = Change.Row
                CellD = Cells(MaxRow, 6)
                HeaderCDE = Cells(600, Change.Column)
                StartMSG = InStr(1, CellD, HeaderCDE)
                If InStr(StartMSG, CellD, Chr(10)) <> 0 Then
                    EndMSG = InStr(StartMSG, CellD, Chr(10)) - 1
                Else
                    EndMSG = Len(CellD)
                End If
                Message = Mid(CellD, StartMSG + Len(HeaderCDE) + 2, EndMSG - StartMSG - Len(HeaderCDE) - 1)
                
                Do While MessageNEW = ""
                MessageNEW = InputBox("Enter Justification for Column [" & Cells(600, Change.Column) & "]", "Mandatory data entry", Message)
                Loop
                
                '-- insert comment in current sheet
                Cells(MaxRow, 6) = Replace(Cells(MaxRow, 6), Message, MessageNEW)
                ActiveSheet.Protect AllowFiltering:=True
                Application.EnableEvents = True
            End If
        Next Change
    End If

On Error Resume Next
Change.Select

End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim SaveVal1 As String
Dim Change As Range

If Target.Column = 3 Then Application.SendKeys ("%{down}")
If Target.Column = 4 Then Application.SendKeys ("%{down}")
If Target.Column = 5 Then Application.SendKeys ("%{down}")
If Target.Column = 7 Then Application.SendKeys ("%{down}")
If Target.Column = 10 Then Application.SendKeys ("%{down}")
If Not Intersect(Target, Range("C1:E600")) Is Nothing Then
For Each Change In Target.Cells
    If Change = "" Then
        TargetIsEmpty = True
    Else
        TargetIsEmpty = False
    End If
Next Change
End If

If Not Intersect(Target, Range("K5:K600")) Is Nothing Then
        If Cells(Target.Row, 11) <> "" Then
            SaveVal1 = Cells(Target.Row, 11)
        End If
End If

End Sub

Open in new window



I hope you can help me and thank you!
craisin

Thank you for helping me!


Please disregard this:
3.) User selects from drop-down in ColE
    a.) user selects N/A then ColF would have:
ColE: this is mandatory entry
    b.) user changes the N/A entry in ColE then ColF is blank but only that protion of the cell is blank the rest of the data stays and is protected


There is no mandatory Entry in ColE.

If user selects N/A in ColC, ColD, ColE it does not include any mandatory entry in ColF

If this was the case then:
User selects N/A in ColC causing ColD and ColE to also equal N/A would have problems because the msgbox would have to go into a spin 3 differnent times.  

Thank you for helping me!

OK..that clears things up a bit.

Stand By.....

Cheers
Chris
Things seem to be happening now....

I keep getting a box saying "Enter Justification for column []"

I gather that data imput is mandatory but I do not understand what column "[]" is supposed to be. Shoudlit be saying something like "column 3".

Also what does "Date of House" and "Date of books" mean?
Don't you mean "Type of House" and "Type of Books"? A date should look like this:
        13/01/2011 or 01/13/11 or something like that.

Could you also now send me a partially completed XLS file so I can see what it SHOULD look like as the data is completed.

Cheers
Chris
Another thing...

I notice the user can DELETE an item in the column, but the data entered in column F is not then deleted. Is that how it is supposed to work?

There is also no "N/A" in the drop-down lists. Should that also be included in the lists by adding "N/A" to cells J5, K5 and L5?

Cheers
Chris
craisin

Thank you for helping me!

You said:
I notice the user can DELETE an item in the column, but the data entered in column F is not then deleted. Is that how it is supposed to work?

No, If user selects a mandatory entry in ColC or ColD then ColF is where the mandatory entry is located.  But If user deletes or chances to non mandatory entry in ColC or ColD then ColF should be deleted as well (see first post orginal):
d.) if user changes the mandatory entry in ColC then ColF is blank but only that protion of the cell is blank the rest of the data stays and is protected

You said:
There is also no "N/A" in the drop-down lists. Should that also be included in the lists by adding "N/A" to cells J5, K5 and L5?
I don't understand your question.

ColJ, ColK, ColL has nothing to do with is post.

Only think of the workbook I posted as an example. All I did was create that basic workbook then I moved the code that I needed to the mainworkbook and now I am trying to debug the mainworkbook.  Notice how some of the workbook posted is also included into the code I posted?  yet some of it is different?

I can not send the original workbook because it is considered Classified.


You said:
Also what does "Date of House" and "Date of books" mean

Nothing they are only headers and they are different in the mainWorkbook.


You said:
I keep getting a box saying "Enter Justification for column []"

That again is in the example workbook and means nothing.

Please look at the orginal first post for what I am looking for?

Thank you for helping me!
craisin

Thank you for helping me!


Please just look at the code that I posted and try to fix it.  But getting examples from the Posted Excel workbook.

The code I posted is almost what I want only there is still some bugs in it.

Thank you for helping me!
I ran the code and received no errors.

Please advise what errors you are getting.
Your question is too hard to understand, I am sorry.

You state "if user selects dropdown in col C and it matches data in Col B"......

There is no data in Col B!

You will have to give a better example of what you are after than what has been given otherwise you wuill never get a good response from any the experts.

I cannot help you unless the conditions you give relate to data on the spreadsheet. Shpouild there be data in Col B?

Cheers
Chris
OK...looking at the code, you are doing a lot of tests on data such as "FRD" , "FOUO", NATO-RD", "N/A" etc.

I gather these should be the values available in the drop-down choices in the columns, otherwise you code will never run.

I am changing the values showing in columns J, K and L on the sample worksheet you provided to reflect this data (not animal names etc.).

Please advise if I am wrong in doing this, but I cannot see how you "IF" tests would ever run, otherwise!

Stand By.....

Cheers
Chris
This is all too confusing, friend!

One line of code you have says:

If Cells(Target.Row, 3) = "N/A" Or Cells(Target.Row, 3) = "FRD" Or Cells(Target.Row, 3) = "FOUO" Or Cells(Target.Row, 3) = "NATO-U" Or Cells(Target.Row, 3) = "NATO-RD" Or Cells(Target.Row, 3) = "Ref" Then

but you don't have ANY data entry in column 3.

I think you will have to explain your problem much clearer, since you are using code from your working example but not relating it to data in the sample spreadsheet. I cannot spend any more time on this unless these matters are clarified, sorry.

Please set out a sample spreadsheet that relates to the sample code you are supplying and then state step by step what is supposed to happen if the user places data in (for example) column 3...what data should then aromatically go in columns 4 and 5.

I you cant express it in code format, just write a few paragraphs stating exactly what you want to happen when someone opens the spreadsheet and selects data from the drop-downs.

We need to really clarify exactly what you are after,s since your requests so-far do not logically fit in with the sample spreadsheet.

I look forward to your response...

Cheers
craisin
(Chris  - Australia)
craisin

Thank you for helping me!
Please advise what errors you are getting

If you look at the:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Section you will see:
If Not Intersect(Target, Range("C1:E600")) Is Nothing Then
For Each Change In Target.Cells
    If Change = "" Then
        TargetIsEmpty = True
    Else
        TargetIsEmpty = False
    End If
Next Change
End If

I ran some test on it and If True then:
CDEtoF
If False:
EditCDEtinF

Now look at:

IsColUpdated = False
    If InStr(1, Cells(Target.Row, "F"), Cells(1, Target.Column)) <> 0 Then
        IsColUpdated = True
    End If

    If Not TargetIsEmpty Or IsColUpdated Then
        EditCDEinF Target
    Else
        CDEtoF Target
    End If
    End If

If True/False then
CDEtoF

means that there is no related text in ColF

If False/True then
EditCDEinF
means that there is related text in ColF so do some edits

If False/False then
it will Error

This is the problem that I am having?

You said:
One line of code you have says:

If Cells(Target.Row, 3) = "N/A" Or Cells(Target.Row, 3) = "FRD" Or Cells(Target.Row, 3) = "FOUO" Or Cells(Target.Row, 3) = "NATO-U" Or Cells(Target.Row, 3) = "NATO-RD" Or Cells(Target.Row, 3) = "Ref" Then

but you don't have ANY data entry in column 3.

But you also said:
I gather these should be the values available in the drop-down choices in the columns, otherwise you code will never run.


This is true there is a drop-down where the user can select.


Please help me and thank you!
I am sorry Armour22015.

I cannot make sense of your stements and so I cannot help you any further.

Good luck in finding another expert who can help.

Kindest regards

Chris
ASKER CERTIFIED SOLUTION
Avatar of ModSquad
ModSquad
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial