Previous Topic Next Topic

If...allow text

Closed
rbmastertitan Posts 6 Registration date Tuesday October 15, 2013 Status Member Last seen November 30, 2013 - Nov 30, 2013 at 01:53 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 1, 2013 at 02:58 AM
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 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 1, 2013 at 02:58 AM
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
0