 |
|
|
|
|
|
Back to: Excel VBA . Got any Excel/VBA Questions? Free Excel Help
Below are Excel macros that will check to see if....Sub IsWorkBookOpen() '''''''''''''''''''''''''''''''''''''''''' 'Written by www.ozgrid.com 'Test to see if a Workbook is open. '''''''''''''''''''''''''''''''''''''''''' Dim wBook As Workbook On Error Resume Next
Set wBook = Workbooks("Personal.xls") If wBook Is Nothing Then 'Not open MsgBox "Workbook is not open", _ vbCritical,"OzGrid.com" Set wBook = Nothing On Error GoTo 0 Else 'It is open MsgBox "Yes it is open", _ vbInformation,"OzGrid.com" Set wBook = Nothing On Error GoTo 0 End If End Sub
Sub DoesWorkBookExist() ''''''''''''''''''''''''''''''' 'Written by www.Ozgrid.com 'Test to see if a Workbook exists '''''''''''''''''''''''''''''''' Dim i As Integer With Application.FileSearch
.LookIn = "C:\MyDocuments" '* represents wildcard characters .FileName = "Book*.xls" If .Execute > 0 Then 'Workbook exists MsgBox "There is a Workbook." Else 'There is NOt a Workbook
MsgBox "The Workbook does not exist" End If End With End Sub
Sub OpenAllWorkbooksInFolder() ''''''''''''''''''''''''''''''' 'Written by www.Ozgrid.com 'Open all found Workbooks in specified folder '''''''''''''''''''''''''''''''' Dim i As Integer With Application.FileSearch .LookIn = "C:\OzGrid Dell" '* represents wildcard characters .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then 'Workbook exists For i = 1 To .FoundFiles.Count Workbooks.Open (.FoundFiles(i)) Next i Else 'There is NOt a Workbook MsgBox "The Workbook does not exist" End If End With End Sub
Sub DoesSheetExist()
'''''''''''''''''''''''''''''''''''''
'Written by www.OzGrid.com
'Test to see if a Worksheet exists.
'''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
On Error Resume Next
Set wSheet = Sheets("Sheet1")
If wSheet Is Nothing Then 'Doesn't exist
MsgBox "Worksheet does not exist", _
vbCritical,"OzGrid.com"
Set wSheet = Nothing
On Error GoTo 0
Else 'Does exist
MsgBox "Sheet 1 does exist", _
vbInformation,"OzGrid.com"
Set wSheet = Nothing
On Error GoTo 0
End If
End Sub
Sub DoesRangeExist()
''''''''''''''''''''''''''''''''''''''''
'Written by www.ozgrid.com
'Test to see if a named range exists.
''''''''''''''''''''''''''''''''''''''''
Dim rRange As Range
On Error Resume Next
Set rRange = Range("MyRange")
If rRange Is Nothing Then 'Doesn't exist
MsgBox "The named range does not exist", _
vbCritical,"OzGrid.com"
Set rRange = Nothing
On Error GoTo 0
Else 'Does exist
MsgBox "The named range does exist", _
vbInformation,"OzGrid.com"
Set rRange = Nothing
On Error GoTo 0
End If
End Sub
Sub IsCellVisible()
'''''''''''''''''''''''''''''''
'Written by www.Ozgrid.com
'Test to see if a cell is filtered by autofilters.
''''''''''''''''''''''''''''''''
Dim bHidden as Boolean
With Sheet1
If .FilterMode = True Then
bHidden = .Range("A5").EntireRow.Hidden
MsgBox "Filters are on and A5 row hidden is " & bHidden
End If
End With
End Sub
Sub HowManyPagesBreaks()
'''''''''''''''''''''''''''''''
'Written by www.Ozgrid.com
'Count how many pages will be printed.
''''''''''''''''''''''''''''''''
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer
iHpBreaks = Sheet1.HPageBreaks.Count + 1
iVBreaks = sheet1.VPageBreaks.Count + 1
iTotPages = iHpBreaks * iVBreaks
MsgBox iTotPages
End Sub
See Also: Excel Duplication Manager Add-in | Excel Number Manager Add-in | Excel Text Manager Add-in | Excel Named Range Add-in Manager | Excel OzGrid Plus Add-in | Excel Time Sheet | Excel Time Wage and Pay book
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS