Userform

[LaDdIe]

Hi,

I have this code that I can't quite get right

If Not Intersect(Target, Range("emp_All")) Is Nothing Then
On Error Resume Next
If Range("A(ActiveCell.Row)") <> "" Then
UserForm1.Show
End If
On Error GoTo 0
Else
On Error Resume Next
Unload UserForm1
Err.Clear
End If

It works except that it appears to ignore 'If Range("A(ActiveCell.Row)") <>
"" ' and proceeds to show the UserForm even if range is empty.

 

[Bob Phillips]

Try

If Not Intersect(Target, Range("emp_All")) Is Nothing Then
On Error Resume Next
If Range("A" & Target.Row) <> "" Then
UserForm1.Show
End If
On Error GoTo 0
Else
On Error Resume Next
Unload UserForm1
Err.Clear
End If

HTH

Bob

 

[FSt1]

hi
not sure but i get an error with your code. so i rewrote it to work on my pc.
Dim r As Long
r = ActiveCell.Row
If Range("A" & r) <> "" Then

i think you have a syntax problem'
regards
FSt1

 

[Ryan H]

This is just a suggestion. I'm not a big fan of using On Error Resume Next.
You are only asking for trouble. 99.999% of the time you can avoid using the
syntax. I would do this instead. This code will test if the Userform you
want to unload is loaded before you Unload it. Hope this helps! If so, let
me know, click "YES" below.

Sub TEST()

If Not Intersect(Target, Range("emp_All")) Is Nothing Then
If Range("A" & ActiveCell.Row) <> "" Then UserForm1.Show
Else
If IsUserFormLoaded(UserForm1) Then Unload UserForm1
End If

End Sub

' Rick Rothsteins code
Function IsUserFormLoaded(UserFormName As String) As Boolean

Dim UF As Object

For Each UF In UserForms
If UCase(UF.Name) = UCase(UserFormName) Then
IsUserFormLoaded = True
Exit Function
End If
Next UF

End Function