Rookie needing help with Macros and formula assistance

[Aaron DOJ]

Hello I am pretty much a VBA rookie, but learning a lot in the last month so please talk to me like I am six and explain as much as possible

I had a thought about putting in a couple macros depending on if a cell is blank or has an "X" I want to have a macro ran depending on which option is used from drop-down list (which has two options, X or nothing/blank) in W7. The below is as far as I could some up with, but I am not strong enough with the VBA force to know if it is somehow possible to put that formula (please see Macro2 below) into a macro or not. If not then nothing else I say below matters as I will have to figure out another way. If it is possible how do I get all of the below to work?? lol

I want to make it so if they check a box using a pull-down and select the "X"in a certain box (W7), that it will empty another cell (T7 (T7 is actually a merged cell from T7:V7 and must stay merged)) so the user can fill in what they need to, but if they un-check the box, the formula will come back into play and run the formula to bring back what was previously there.

'Sheet1
' Private Sub Worksheet_Change(ByVal Target As Range)
' Set Target = Range("W7")
' If Target.Value = "x" Then
' Call Macro1
' End If
' If Target.Value = "" Then
' Call Macro2
' End If
'End Sub

'Module 1
'Sub Macro1()
' Sheets("Sheet1").Range("T7").ClearContents
' MsgBox Please enter your PCA in Section 3c.
'End Sub

'Sub Macro2()
' =VLOOKUP(M6,Sheet2!L3:M167,2,FALSE)
'End Sub



Thank you for your time,
Aaron

 

[Fluff]

Hi & welcome to MrExcel
How about

 Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) <> "W7" Then Exit Sub
   If Target.Value = "x" Then
      Range("T7:V7").ClearContents
      MsgBox "Please enter your PCA in Section 3c"
   Else
      Range("T7").Formula = "=VLOOKUP(M6,Sheet2!L3:M167,2,FALSE)"
   End If
End Sub

 

[Joe4]

Welcome to the Board!

In these kind of event procedures, you do not set the Target cell - that is automatic.
Target is the cell which triggered this code to run. So on a Worksheet_Change event procedure, it is the cell that was just updated.
So what we want to do is to check if it was cell W7 that was updated.

Here is the code that I think will do what you ask (clear T7:V7 when and "x" is entered/picked in cell W7):

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if cell W7 was updated
    If Target.Address(0, 0) = "W7" Then
'       Check to see if value of W7 is "x"
        If Target = "x" Then
'           Clear cell T7
            Range("T7:V7").ClearContents
        Else
'           Put formula in T7
            R[COLOR=#333333]ange("T7").Formula = "=VLOOKUP(M6,Sheet2!L3:M167,2,FALSE)"[/COLOR]
        End If
    End If
    
End Sub

 

[Aaron DOJ]

Thank you both for the warm welcome and SUPER fast reply!!

I think somehow I am missing something, I tried both of your codes and get the same result:
A new window pops open to attach a file and at the top it says "Update Values: Sheet2"

I am not sure what I could pass on to either of you to assist with this other than, does it matter that Sheet2 is still Sheet2, but the Tab is called "PCA Look up"?

-Aaron

 

[Fluff]

Ok, how about

 Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) <> "W7" Then Exit Sub
   If Target.Value = "x" Then
      Range("T7:V7").ClearContents
      MsgBox "Please enter your PCA in Section 3c"
   Else
      Range("T7").Formula = "=VLOOKUP(M6,'PCA Look up'!L3:M167,2,FALSE)"
   End If
End Sub

 

[Aaron DOJ]

Oh wow do I feel sheepish...This issue with the VLOOKUP has been on the back burner for so long I totally forgot I have modified the spreadsheet since the original formula was created...
=VLOOKUP(O7,'PCA Look up'!K3:L166,2,FALSE) is the current formula I have in T7.

After updating the formula in both of your recommendations I am not getting the error, but nothing was happening either. No MsgBox and T7 still holds the formula...but its progress

 

[Aaron DOJ]

Oddly (to me) when I delete the formula in T7 and no matter which option the drop-down is on or which option I select the formula comes back.

 

[Joe4]

Is your "X" upper-case or lower-case?
It makes a difference.

Either your code needs to match exactly what is on your sheet, or you need to update the code like this so it is not context-sensitive.

        If Lcase(Target) = "x" Then

 

[Aaron DOJ]

Joe thank you for pointing that out, funny how the small details can slip by. In my original post I have capital X and I didn't realize that both of you had been putting in lower case x. I added that to the code and it works. So to be clear Joe and Fluff rock I can get rid of all that other code/macro I started and my new code is:

If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) <> "W7" Then Exit Sub
If LCase(Target) = "x" Then
Range("T7:V7").ClearContents
MsgBox "Please enter your PCA in Section 3c"
Else
Range("T7").Formula = "=VLOOKUP(O7,'PCA Look up'!K3:L166,2,FALSE)"
End If

 

[Joe4]

funny how the small details can slip by

You are welcome. Glad we were able to help.
Yes, the devil usually is in the details!