VBA If/Then with formula and locks/unlocks cell

[csimarketing]

I am new to writing macros and am struggling with this one.

I would like the following to happen automatically:

If Cell "C8" equals "NEW" then unlock Cell "F8" and allow someone to type in any value in Cell "F8".
If Cell "C8" does NOT equal "NEW" then lock Cell "F8" and run this formula: "=VLOOKUP(C8,Dropdowns!A3:B273,2,FALSE)"


I originally had a dependent dropdown list where C8 was our customer # and F8 was the customer name and autopopulates based on the value in C8.

However, now we need to be able to enter in a New Customer that is not in the predetermined dropdown, while also keeping the autopopulation function above if an existing customer is entered in C8.

Here is my current VBA, but it doesn't work...





Private Sub Workbook_Change(ByVal Target As Range)
If Range("C8") = "NEW" Then
Range("F8").Locked = False
ElseIf Range("C8") <> "NEW" Then
Range("F8").Locked = True
ElseIf Range("C8") <> "NEW" Then
Else: Range("F8").Formula = "=VLOOKUP(C8,Dropdowns!A3:B273,2,FALSE) "

End If
End Sub



Any help would be much appreciated! Thanks!

 

[Fluff]

How about

Private Sub Workbook_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C8" Then
      If Target.Value = "NEW" Then
         Range("F8").Locked = False
      Else
         Range("F8").Formula = "=VLOOKUP(C8,Dropdowns!A3:B273,2,FALSE)"
         Range("F8").Locked = True
      End If
   End If
End Sub

 

[csimarketing]

Unfortunately the suggested code does not work.

This is a protected document so that might be having an effect on the code... but when I tested the above VBA protected or not, still did not work.

Thanks for trying though.

 

[Fluff]

Please define "did not work".

 

[csimarketing]

Hi Fluff,

To clarify... The cells do not lock and unlock based on the value of C8, and when C8 equals anything greater than 1 and not "NEW", it does not run the required vlookup formula.

 

[Fluff]

Ok.
Do you have the code in the relevant sheet module? Right click the tab you want this to work on & select view code. Do you see the code in the window that opens up?
Also are you typing NEW into C8 as opposed to New or new or anything else?

 

[csimarketing]

I checked the code how to described and yes, it shows up in the window. And C8 is a data validation drop down based on a predetermined list. You can type in the customer # and the goal is to have the customer name auto-populate in F8 based off of the customer # in C8. I originally had the vlookup in F8 and it worked great. But now we need to have the flexibility to type in a temporary new customer name (aka C8 is "NEW") and not update the dropdown options each time. Does that make sense? Sorry, this is hard to explain without showing you. Is there a way I can share a test document on here for you to see?

 

[Fluff]

We do not allow people to upload workbooks to the site, but there are some add-ins available here that enable you to include a small sample of data within your post https://www.mrexcel.com/forum/about-board/508133-attachments.html
However in this instance it would probably be better if you could upload a copy of your workbook (desensitised if needed) to a share site such as OneDrive, Dropbox, mark for sharing & post the link to the thread.

 

[csimarketing]

Here is a shared link to a basic version of what I am trying to do: https://drive.google.com/file/d/1S1q-SiTTHEXFK3sKT5k6b6WKm-R6wsuT/view?usp=sharing

 

[Fluff]

Thanks for that, any chance of the password as well?