If...allow text

[Closed]
Report
Posts
6
Registration date
Tuesday October 15, 2013
Status
Member
Last seen
November 30, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
A1 has a drop down list with HM01 and Off Spec, B2 = text depending what is selected from the list in A1.
So B2 =if(A1"HMO1",'DISHCODES'!B3).....when I select "off spec" I want to allow B2 to be written in without deleting the formula in B2.

Can you help please!!!

Tim

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
I wonder whether worksheet solution (formula) can be done . once you type something else in B2 the formula in B1 will go away

do this experinmbt

In a blanks sheet create validation in A1' (these two items only)

right click the TAB of this sheet and click view code and in the window that comes up copy this EVENT CODE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Application.enableevents = False
On Error GoTo enableevents
If Target = "HM01" Then
Range("B2") = "'DISCHODES'B3"
Else
Range("B2") = InputBox("type whatever you want to entere i B2")
End If
enableevents:
Application.enableevents = True

End Sub


Now go to the sheet enter in A1 using validation list and see what happens in B2
try both the choices in validation list and again repeat.

when you choose "Off psec" the code will ask you what to enter in B2. type the item(string or number or date or Boolean) and hit enter key
see what is entered in B2

feedback please