VBA Event Handler for specific cell changes

[s0up2up]

Hey guys,

Like the question states, is there an even handler that will enact a macro if a value is entered into a specific cell?

Cheers for any help!

Cheers,
s0up2up

 

[repairman615]

Hello,

Try like:






<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> Changed = Range("A1")<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Changed) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'''Your Code Here</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> Changed = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[s0up2up]

That works a treat! Now if you name a cell or cell range, you should be able to reference it in VBA just by that name right? Eg: Name is Entity_Type? should be able to be accessed by range(Entity_Type).select, or something along those lines, right?

 

[repairman615]

Reference named ranges like:

Range("Entity_Type")

Don't forget the quotes "".

 

[s0up2up]

Cool. Thanks Mate.

Now what happens then I want to track multiple changes like this?

 

[repairman615]

I think I do not fully understand your question. If you are wondering how to make this trigger upon multiple different cells and / or ranges then that would be like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range
Set Changed = Range("A1,C10,E1")
    If Not Intersect(Target, Changed) Is Nothing Then
        '''Your Code Here
        MsgBox 1
    End If
Set Changed = Nothing
End Sub

This will look at Cells A1, C10, and E1 for changes.


Does that help?

 

[s0up2up]

I hate to ask you to go through screens of code but I have an error for one that features a multicell worksheet change macro. The macro is below:

Private Sub worksheet_change(ByVal target As Range)
Dim rngEntityType As Range
Dim rngLargeCompany As Range
Dim rngNoCount As Range
Dim rngYesCount As Range

Set rngEntityType = Range("Entity_Type")
    
    'When entity type is recorded it will show the correct fields to enter the data
    If Not Intersect(target, rngEntityChanged) Is Nothing Then
         
         If Range("Entity_Type").Value = "Company" Then
            
            'Shows Large Company questionaire, if company is selected
            ActiveSheet.Range("LargeCompany").Hidden = False
         
         Else
            
            'Shows General Purpose questionaire, if company is not selected
            ActiveSheet.Range("GeneralPurpose").Hidden = False
                        
         End If
              
    End If

 Set rngEntityChanged = Nothing

Set rngLargeCompany = Range("LargeCompany5")

       'When this last bit of data is entered it will move you onto next questionaire
    If Not Intersect(target, rngLargeCompany) Is Nothing Then
         
         If Range("LargeCompany4") = "Yes" Or Range("LargeCompany5") = "Yes" Then
                 
                 'Shows Publically Accountable questionaire,
                 'if yes is entered in question 4 or 5 of the Large Company Questionaire
                 ActiveSheet.Range("PublicallyAccountable").Hidden = False
                                  
          End If
          
         Else
            
            For Each cell In Range("LargeCompany1, LargeCompany2, LargeCompany3")
               If cell.Value = "No" Then rngNoCount = rngNoCount + 1
            Next cell
         
                    If rngNoCount >= 2 And Range("LargeCompany4") = "No" And Range("LargeCompany5") = "No" Then
                        MsgBox "Exempt Company" & vbNewLine & "(Financial Reporting Act)"
                    
                    rngNoCount = 0
                                      
                    
                     
         Else
           
            For Each cell In Range("LargeCompany1, LargeCompany2, LargeCompany3")
               If cell.Value = "Yes" Then rngYesCount = rngYesCount + 1
            Next cell
         
                    If rngYesCount >= 2 And Range("LargeCompany4") = "No" And Range("LargeCompany5") = "No" Then
                          ActiveSheet.Range("PublicallyAccountable").Hidden = False
                    
                    rngYesCount = 0
                                      
                    End If
            End If
            
         End If
              
    

End Sub

So the aim of the code is to reveal certain cells when certain answers are entered into cells in a dynamic questionaire.

At the moment its getting hung on the line in bold, and I just can't figure out why.

Anyone have any idea?

 

[s0up2up]

Hopefully no one thought about this one too hard. Figured it out.

rngEntityChanged was supposed to be rngEntityType in the intersect line

 

[repairman615]

Glad you got it working!


I noticed you are testing for "Yes" and "No" within your code. As you may know this is case sensitive.

Ucase() and Lcase() will convert a string to UPPERCASE or lowercase. From there you can test if the string matches.

This comes in handy if you may get some answers in UPPER, lower, Proper, or ShAnAgIn form.




Below is a code that shows a test in case sensitivity.




<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CaseTest()<br><br><SPAN style="color:#00007F">Dim</SPAN> str1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> str2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>str1 = "Yes"<br>str2 = "yes"<br><br>MsgBox "Does """ & str1 & """  =  """ & str2 & """???" & vbNewLine & vbNewLine & (str1 = str2)<br><br><br><br><SPAN style="color:#007F00">''' Use Ucase() to convert to  the strings "Yes" and "yes" UPPER case..... ="YES"</SPAN><br>MsgBox "Does Ucase(""" & str1 & """) and Ucase(""" & str2 & """)  = " & """YES""" & "???" & _<br>        vbNewLine & _<br>        vbNewLine & (UCase(str1) = "YES" And UCase(str2) = "YES")<br><br><SPAN style="color:#007F00">'''Here is how it Ucase would look:</SPAN><br><SPAN style="color:#007F00">'''If Ucase(str1) = "YES" Then.....</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[s0up2up]

Valid point. Thanks for the point, I'll keep it in mind.

At the moment, i've used the data validation tool to just give "Yes"'s or "No"'s... That way I didn't have to deal with it.