Can I use VBA to change a cell that already contains a data validation

[AwesomeSteph]

I have an excel sheet that users have to input data to access their forms, on the first sheet in B2 the user picks a project and in B4 the user will pick a sub-project. In B24 there is a data validation determined upon what is selected from B2. My question is can I overwrite the drop down and have vba input something specific in B24 (not the drop down) if a specific sub-project is selected from B4?

Currently the source for the list in B24 is as follows (selections and named ranges changed due to sensitive information, however it is currently at max length):
=IF(B2="ABC",ABC,IF(B2="DEF",DEF,IF(B2="GHI",GHI,IF(B2="JKL",JKL,IF(B2="MNO",MNO,IF(B2="PQR",PQR,""))))))

Both B2 and B4 are data validation lists.
What I want is for the above formula to work unless B4 contains xyz then I don't want a drop down in B24 I want just the number associated with that sub-project.

 

[AwesomeSteph]

I've tried

Private Sub worksheet_change(ByVal target As Range)
Application.ScreenUpdating = False
With Range("B24").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=IF(B2=NGPF,NGPFCHARGE,IF(B2=OCE,OCECHARGE,IF(B2=F135,OCECHARGE,IF(B2=Service Bulletins,SBCHARGE,IF(B2=IPD,IPDCHARGE,IF(B2=Advanced Militarty,AMCHARGE,IF(B4=PW1100/1400,8203-18/8203-16,IF(B4=PW1200/1700,8203-17/8203-15,IF(B4=PW1500/1900,8203-19/8203-14,"")))))))))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


Application.ScreenUpdating = True
End Sub

but get an error on line 6

 

[Logit]

.
I believe the error you are encountering relates to the lack of quotations marks around the terms in your formula.

Try this :

=IF(B2="NGPF","NGPFCHARGE",IF(B2="OCE","OCECHARGE",IF(B2="F135","OCECHARGE",IF(B2="Service Bulletins","SBCHARGE",IF(B2="IPD","IPDCHARGE",IF(B2="Advanced Military","AMCHARGE",IF(B4="PW1100/1400","8203-18/8203-16",IF(B4="PW1200/1700","8203-17/8203-15",IF(B4="PW1500/1900","8203-19/8203-14","")))))))))

Also it is not necessary to place the formula into a macro in the WORKSHEET_CHANGE event for the sheet. You can place the formula directly into Cell B24.

 

[AwesomeSteph]

Can't use quotation marks in the vba, the problem with my code is the named ranges. I can't put your code in B24 because I need drop downs and I can't put your code in source for Data Validation because it is too long.

 

[Logit]

.

Can't use quotation marks in the vba

When using quotation marks in VBA code, you have to use double quotes. For example: If in a regular formula entered directly into a cell on the sheet, this formula: =IF(B2="No","22") ... in VBA
code would be "=IF(B2=""No"",""22"")

I can't put your code in B24 because I need drop downs

Your post doesn't indicate B24 has a dropdown ... ????

and I can't put your code in source for Data Validation because it is too long.

Hmmm ... I don't receive an error here related to that.



At this point I am not certain how to assist.

 

[AwesomeSteph]

Thanks for trying to help. Sorry my question wasn't clear. B2, B4, and B24 all have drop downs and the drop down in B4 is dependent upon what is selected in B2 I have no problem with that my problem is I want the drop down in B24 to be overwritten with an exact text that I pick if a certain selection in B4 is selected. B4 drop down is also dependent upon B2. Maybe this isn't possible...

 

[Logit]

.

Let me try to understand. You have B2, B4 & B24 as dropdowns that are pre-populated with various terms that can be selected from the drop down.

B4 is dependent upon what is selected in B2.

B24 is not dependent on anything but you want it dependent upon B4.

Is that accurate ?


Or .... are you saying ...

Even though B24 has a list of pre-populated terms that can be selected from the dropdown ... you want to MANUALLY
override those terms and enter something else in B24 ?

 

[Logit]

.

If it is your goal to :

you want to MANUALLY
override those terms and enter something else in B24 ?

Then this macro will accomplish it:

Option Explicit


Sub ovrRideDropDown()
Dim x As Variant


x = InputBox("Please enter term.", "What Term ?")


With Sheets(1)
    With ActiveCell
          .Value = x
    End With
End With


End Sub

Click the dropdown cell, then the command button to activate the macro and proceed.

 

[AwesomeSteph]

Not exactly. B2 is selected from a static drop-down, B4 is different drop-downs dependent upon the selection is B2 (formula in data validation source).

B24 is also different drop-downs, dependent upon the selection in B2.

What I would like is that if B24 could be overwritten (so the drop-downs are not available and text that I chose is put in B24) if a very specific section is chosen from B4 (2 options out of 50).

If xyz is selected in B4, can that overwrite the formula I have in B24 deciding what the drop down would be (Data validation source formula) using vba?

.

Let me try to understand. You have B2, B4 & B24 as dropdowns that are pre-populated with various terms that can be selected from the drop down.

B4 is dependent upon what is selected in B2.

B24 is not dependent on anything but you want it dependent upon B4.

Is that accurate ?


Or .... are you saying ...

Even though B24 has a list of pre-populated terms that can be selected from the dropdown ... you want to MANUALLY
override those terms and enter something else in B24 ?

 

[Logit]

.
Wouldn't the macro I posted in my last post do this for you ?

You would select B24, click a command button and enter the term you want displayed in B24.