if isempty question

[devj364]

Ok, so I am relatively new to vba and i need to show a userform if a cell is empty, but for some reason it is not running correctly. Could i get some help or advice on how to better handle please.


Sub Autpen()


Worksheets("Report Disposition").Activate


If IsEmpty("D3:J3") Then


UserForm1.Show


Else


Worksheets("Variable Data").Activate


End If


End Sub

 

[Trebor76]

Hi devj364,

Welcome to MrExcel!!

I don't think the IsEmpty function can handle a range so try this that simply uses the COUNTA worksheet function:

Option Explicit
Sub Auto_Open()

    Worksheets("Report Disposition").Activate
    If Evaluate("COUNTA(D3:J3)") = 0 Then
        UserForm1.Show
    Else
        Worksheets("Variable Data").Activate
    End If

End Sub

Must say seeing Autpen brought a smile as I used that in the 90's and haven't seen it for a long time Most people now use the Workbook_Open event macro.

Thanks,

Robert

 

[devj364]

thanks that helped out thanks!!!

I am still figuring things out. But. if you have a second or two i would like to know the difference in Autpen and Workbook_Open event?

 

[Trebor76]

Glad we got it sorted

To use the workbook event macro follow these five steps:

1. Open the Visual Basic Editor (Alt + F11)
2. Click on ThisWorkbook in the Project - VBAProject left-hand panel
3. Click on the left-and side drop-down and select Workbook
4. Click on the right-hand side drop-down and select Open (this may have already been selected by default)
5. Put the code you want to run whenever the workbook is opened here

Note your Autpen macro will work too but it is old school now and there's other workbook events you can also use using the steps above.

HTH

Robert