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!
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!
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 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!
ASKER
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
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
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)
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
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
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
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...
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...
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?)
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
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....
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....
ASKER
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!
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!
ASKER
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:
And this:
And This:
I hope you can help me and thank you!
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
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
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
I hope you can help me and thank you!
ASKER
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!
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
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
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
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
ASKER
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!
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!
ASKER
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!
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.
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
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
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)
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)
ASKER
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!
Thank you for helping me!
Please advise what errors you are getting
If you look at the:
Private Sub Worksheet_SelectionChange(
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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