Show Userform if Two conditions Met

[SteelNuttz]

I want to show userform1 if two cells meet certain criteria.

If Cell A1 = "AFC"
and Cell b1 = "Patriots"

that's the only time I want the userform1 to show, is there an easy way to do this?

 

[Grant22]

If you know VBA you could put a command for when that worksheet (or the entire workbook if need be) is activated then userform1 shows:

Private Sub Worksheet_Activate()
If Range("A1") = "AFC" & Range("B1") = "Patriots" then
userform1.show
End Sub

Else, you could always put a button on the sheet and assign a macro or IF statement to it that will make the userform1 show if the cells meet the criteria. Keep in mind, if you don't use proper case in the example above it won't work. I.E. "Patriots" would work but "patriots" wouldn't, if it matters. Hope this helps!

 

[SteelNuttz]

Thanks for the reply.

I guess I would only want it to run the macro when B1 is changed. These cells are also part of a table. I'm not sure if this helps to try and help me work out some kinks.

 

[Grant22]

I pulled this from Microsoft's help site, it should allow you to set your cell range ("B1") and then open the userform1 (userform1.show) only if B1 changes. Try and see if it works for what you're needing:

<code>Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("B1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
userform1.show


End If
End Sub</code>

 

[SteelNuttz]

Thanks again Grant - this still doesn't work though. I have other macros that run based on a cell having specific text in it using the "Select Case" function...anything like this that could help me?

 

[Grant22]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B1") Then
'Determine if the work "Patriots" is contained within cell B1
If InStr(1, Range("B1"), "Patriots") > 0 Then
UserForm1.Show
End If
End If
End Sub

It assumes that B1 needs to show userform1 but only if B1 has Patriots as the value. Hopefully this solves your issue!