• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Open Form if worksheet is empty

Hi there.  I have a form in Excel that I want to appear if the user opens up a worksheet in their workbook that is empty.  I tried putting the code under the Workbook_Activate.  When I F8 through this code it works, but it doesn't work when I'm in my Excel workbook and I click on a sheet to make it active.

Any ideas what I've done wrong? I've commented out my form, and just put a messagebox to appear but that doesn't work either.

Private Sub Workbook_Activate()
Call isWorkSheetEmpty
End Sub
 
Sub isWorkSheetEmpty()    
Dim result As Long
Application.DisplayAlerts = False
 
   result = Application.WorksheetFunction.CountA(ActiveWorkbook.ActiveSheet.Range("a:iv"))
   If result = 0 Then
       ' Call ShowHeaderForm
    MsgBox "empty"
   End If
 
Application.DisplayAlerts = True
 
End Sub

Open in new window

0
Fi69
Asked:
Fi69
  • 5
  • 2
1 Solution
 
Patrick MatthewsCommented:
Have you tried using the SheetActivate event?
0
 
Fi69Author Commented:
Yay that did something, but it errored on this line:

result = Application.WorksheetFunction.CountA(ActiveWorkbook.ActiveSheet.Range("a:iv"))

any ideas why?
0
 
Fi69Author Commented:
It's okay I worked it out by putting the below.  Thanks so much for your help!!!

Private Sub workbook_SheetActivate(ByVal Sh As Object)

If Sh.Type = xlWorksheet Then
    Call isWorkSheetEmpty
    Else
        'do nothing
    End If
End Sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Fi69Author Commented:
Thanks again!
0
 
DevomCommented:
You shouldn't need the SheetActivate command for what you're using, the code works fine on my excel.  Does it matter where your code is? Does it have to work on different workbooks?  

Try putting the code in a worksheet and changing the line where you set result to be
result = Application.WorksheetFunction.CountA(Me.Range("a:iv"))
0
 
Fi69Author Commented:
I'm only a newbie. My Workbook is an XLT (template). When a user clicks on a sheet in the open workbook that has nothing in it I want my form to open up to prompt them to do something.  If I put it in a worksheet, how will this be activated, ie will it open up automatically when they click on an empty worksheet, or would they specifically have to run the code?
0
 
DevomCommented:
I misread the problem here and didn't notice we were working with events :/.  The Workbook_SheetActivate event is exactly what you needed, sorry for the confusion.
0
 
Fi69Author Commented:
No problem. Thanks for helping.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now