Solved

Open Form if worksheet is empty

Posted on 2009-07-07
8
188 Views
Last Modified: 2012-05-07
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
Comment
Question by:Fi69
  • 5
  • 2
8 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24793745
Have you tried using the SheetActivate event?
0
 

Author Comment

by:Fi69
ID: 24793966
Yay that did something, but it errored on this line:

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

any ideas why?
0
 

Author Comment

by:Fi69
ID: 24794017
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
 

Author Closing Comment

by:Fi69
ID: 31600565
Thanks again!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Expert Comment

by:Devom
ID: 24794053
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
 

Author Comment

by:Fi69
ID: 24794104
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
 
LVL 3

Expert Comment

by:Devom
ID: 24794281
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
 

Author Comment

by:Fi69
ID: 24794360
No problem. Thanks for helping.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Formula 4 28
macro for closing opened workbook 6 20
From msAccess  SaveAs an excel file SILENTLY without opening excel 4 29
Excel sheet question 12 26
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now