Previous Topic Next Topic

If...allow text

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

Réponse 1 / 1
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
1 Dec 2013 à 02:58
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