Run Time error

rjplante

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
I have a macro that is called from another macro that when it runs I get the following error message:

------------------------------

Run-Time error '1004':

Unable to set the Visible property of the Worksheet class

-------------------------------

Then it flags the line in red in the first code box below:

The macro runs, brings me to the main page, hides the other worksheets, and completes everything else written, and then it flags the error. I don't know what to look for or how to resolve it.

Thanks in advance for any help with this bug, I mean design feature!

Robert
-----------------------------------------

Rich (BB code): 
Sub Intro_page()

Application.ScreenUpdating = False

On Error Resume Next
For Each s In Worksheets
    s.Visible = False
    If s.Name = "Intro Page" Then s.Visible = True
Next s

Sheets("Intro Page").Activate
    With ActiveWindow
        .ScrollColumn = 1
        .ScrollRow = 1
    End With

ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub
The above macro gets called from this macro.

Rich (BB code): 
Sub MSTS_UPDATE_CHECK_EXIT()

'Check to see if main field says 'YES' and Line # is Blank

If Range("BE7").Value = "YES" And Range("BE5") = "" Then
Call MSTS_MAIN_Update
End If

If Range("BE14").Value = "YES" And Range("BF14") = "" Then
Call MSTS_T12_Update
End If

If Range("BE15").Value = "YES" And Range("BF15") = "" Then
Call MSTS_T18_Update
End If

If Range("BE16").Value = "YES" And Range("BF16") = "" Then
Call MSTS_T24_Update
End If

Call Intro_page

End Sub
The other macros listed appear below.
Rich (BB code): 
Sub MSTS_MAIN_Update()

'   Ask user if they want to update the Master Stability Tracking Sheet.

Application.EnableEvents = False

i = MsgBox("Do you want to update the Master Stability Tracking Sheet?", vbYesNo + vbExclamation + vbDefaultButton2)

If i = 7 Then 'NO
Application.EnableEvents = True
Application.ScreenUpdating = True
Call Intro_page
Exit Sub

ElseIf i = 6 Then 'YES
Application.ScreenUpdating = False

File1 = ActiveWorkbook.Name 'defines the current workbook name as File1

Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BD11:BO11").Copy

'open Master Stability Tracking Sheet workbook

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB  QC\510k Lot Testing\PCB QC Trendline Data\Master Stability Tracking  Sheet.xlsm"

'   Check to see if first row is blank and if it is paste data in first row.

If Range("B4").Value = "" Then
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

'   If first row is not blank, go to the first empty row.

Else
Range("B3").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

End If

'   transfer row number back to the main workbook.

Range("B3").End(xlDown).Offset(0, -1).Copy 'From MSTS trendline workbook

With Workbooks(File1)
    .Sheets("QC5003.8 PCB Stab Rec - Storage").Visible = True
    .Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BE5").PasteSpecial Paste:=xlPasteValues
End With

ActiveWorkbook.Save
ActiveWorkbook.Close    ' close MSTS workbook

Workbooks(File1).Activate

'   record the date MSTS was updated.

Sheets("QC5003.8 PCB Stab Rec - Storage").Range("AH31").Value = Date
Sheets("QC5003.8 PCB Stab Rec - Storage").Visible = False

Application.EnableEvents = True
Application.ScreenUpdating = True

Call Intro_page

MsgBox "Data transfer is complete."

End If

End Sub

Rich (BB code): 
Sub MSTS_T12_Update()

Application.ScreenUpdating = False

File1 = ActiveWorkbook.Name

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB  QC\510k Lot Testing\PCB QC Trendline Data\Master Stability Tracking  Sheet.xlsm"
Range("Z1").ClearContents

Workbooks(File1).Activate

Sheets("QC5003.8 PCB Stab Rec - Storage").Visible = True
Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BE5").Copy

Application.ScreenUpdating = True
Workbooks("Master Stability Tracking Sheet.xlsm").Activate
Application.ScreenUpdating = False
Sheets("Stability").Range("Z1").PasteSpecial Paste:=xlPasteValues

'Loop to find row
Range("A3").Select

Do

If ActiveCell.Value <> Range("Z1").Value Then
Selection.Offset(1, 0).Select
End If

Loop Until ActiveCell.Value = Range("Z1").Value

ActiveCell.Offset(0, 13).Select
ActiveCell.Value = Date

ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks(File1).Activate

Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BF14").Value = Date

Application.ScreenUpdating = True

Call Intro_page

End Sub

Rich (BB code): 
Sub MSTS_T18_Update()

Application.ScreenUpdating = False

File1 = ActiveWorkbook.Name

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB  QC\510k Lot Testing\PCB QC Trendline Data\Master Stability Tracking  Sheet.xlsm"
Range("Z1").ClearContents

Workbooks(File1).Activate

Sheets("QC5003.8 PCB Stab Rec - Storage").Visible = True
Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BE5").Copy

Application.ScreenUpdating = True
Workbooks("Master Stability Tracking Sheet.xlsm").Activate
Application.ScreenUpdating = False
Sheets("Stability").Range("Z1").PasteSpecial Paste:=xlPasteValues

'Loop to find row
Range("A3").Select

Do

If ActiveCell.Value <> Range("Z1").Value Then
Selection.Offset(1, 0).Select
End If

Loop Until ActiveCell.Value = Range("Z1").Value

ActiveCell.Offset(0, 14).Select
ActiveCell.Value = Date

ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks(File1).Activate

Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BF15").Value = Date

Application.ScreenUpdating = True

Call Intro_page

End Sub

Rich (BB code): 
Sub MSTS_T24_Update()

Application.ScreenUpdating = False

File1 = ActiveWorkbook.Name

ChDir "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB QC\510k Lot Testing\PCB QC Trendline Data"
Workbooks.Open Filename:= _
    "\\Ferrari\common\Product QC\BioPrep QC\PrepaCyte-CB\PrepaCyte-CB  QC\510k Lot Testing\PCB QC Trendline Data\Master Stability Tracking  Sheet.xlsm"
Range("Z1").ClearContents

Workbooks(File1).Activate

Sheets("QC5003.8 PCB Stab Rec - Storage").Visible = True
Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BE5").Copy

Application.ScreenUpdating = True
Workbooks("Master Stability Tracking Sheet.xlsm").Activate
Application.ScreenUpdating = False
Sheets("Stability").Range("Z1").PasteSpecial Paste:=xlPasteValues

'Loop to find row
Range("A3").Select

Do

If ActiveCell.Value <> Range("Z1").Value Then
Selection.Offset(1, 0).Select
End If

Loop Until ActiveCell.Value = Range("Z1").Value

ActiveCell.Offset(0, 15).Select
ActiveCell.Value = Date

ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks(File1).Activate

Sheets("QC5003.8 PCB Stab Rec - Storage").Range("BF16").Value = Date

Application.ScreenUpdating = True

Call Intro_page

End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Have you tried starting the code with

Code: 
Dim s As Worksheet

Or you may have protected the workbook structure, again this is easily overcome with code
 
Upvote 0
maybe
Code: 
[/FONT]
[FONT=Courier New]dim s as worksheet[/FONT]
[FONT=Courier New]If s.Name = "Intro Page" Then s.Visible = True[/FONT]
[FONT=Courier New]For Each s In Worksheets[/FONT]
[FONT=Courier New]    [COLOR=red]s.Visible = False[/COLOR][/FONT]
[FONT=Courier New]Next s[/FONT]
[FONT=Courier New]
 
Upvote 0
Good point, but I think

Code: 
Dim s As Worksheet
Sheets("Intro Page").Visible = True
For Each s in Worksheets
If s.Name <> "Intro Page" then s.Visible = False
Next s
 
Upvote 0
When I tried that I get the following error:

------------------------
Run-Time error '1004':

Method 'Visible' of object '_Worksheet' failed

-------------------------

More mystery!
Robert
 
Upvote 0
rjplante said:
When I tried that I get the following error:
Click to expand...
rjplante said:
------------------------
Run-Time error '1004':

Method 'Visible' of object '_Worksheet' failed

-------------------------

More mystery!
Robert
Click to expand...

then try this way....
and make sure you have sheet name "Intro Page" to be visible as 1 of the sheet needs to be visible...
Code: 
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub test1()[/FONT]
[FONT=Courier New]Dim s As Worksheet[/FONT]
[FONT=Courier New]For Each s In ActiveWorkbook.Worksheets[/FONT]
[FONT=Courier New]If s.Name <> "Intro Page" Then[/FONT]
[FONT=Courier New]s.Visible = xlSheetVeryHidden[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]Next s[/FONT]
[FONT=Courier New]End Sub[/FONT]
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Replaced CLICK event for CALL event but now see Run time error
Replies
6
Views
297
igold
igold
S
  • Solved
Run-Time Error '438' when populating e-mail addresses from within Excel VBA
Replies
1
Views
299
rollis13
rollis13
D
  • Solved
Run-time error 9, Subscript out of range - Copying sheet from closed workbook to active workbook as new sheet
Replies
9
Views
575
dvward
D
A
  • Question Question
VBA run-time error
Replies
2
Views
458
Aaro23
A
J
  • Question Question
Run-time error '1004': The extract range has a missing or invalid field name.
2
Replies
15
Views
1K
josros60
J

Forum statistics

Threads
1,224,521
Messages
6,179,290
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top