Problem running sub on a User Form

[Brian]

I have a User Form that has (21) check boxes for making the worksheets in the
workbook visible or hidden. Also on this User Form there is an Update Control
Button that is clicked to transfer the Data from the User Form to the
Worksheet.

My problem is if the Worksheet is hidden and I try to update the workbook I
get an error message.

Run_Time Error 1004
Select Method of worksheet class failed

The following code works perfect as long as all the worksheets are visable,
but once you hide one of the worksheets I get above error message. I am not
sure how to code this to check if worksheet is hidden bypass that sub and
continue on.

Sub Update_Installer_Forms5()

For i = 601 To 620
On Error GoTo NoSelection
If UserForm1("Battery_String_Qty_" & i) Then

On Error GoTo 0 'Reset error trapping
Run "Battery_String_" & Mid(i, 2)
Exit For

End If
Next i
Exit Sub

NoSelection:
MsgBox "No Batteries Selected." & vbLf _
& "Processing Terminated."
Exit Sub
End Sub

 

[Dave Peterson]

You didn't share the code that actually had the problem.

But I bet you're selecting the objects (checkboxes or ranges or whatever).

Don't do that.

Instead just work with directly.

with workbooks("somenamehere.xls").worksheets("this is hidden")
.range("A1").value = me.textbox1.value
.checkboxes("checkbox 01").value = me.checkbox1.value
end with

 

[FSt1]

hi
just to add to dave's post...

Run_Time Error 1004
Select Method of worksheet class failed

you can only select from the active sheet. if the sheet is hidden then
obviously that is not the active sheet. I think that is what is generaging
the error. as dave is pointing out, you don't really have to select to
update. otherwise you will have to add code to unhide the sheet in order to
select and update.

Regards
FSt1

Regards
FSt1

 

[Brian]

Sorry about that. Here is one of the (20) codes that runs depending on which
Option Button is chosen.

If I choose lets say 5 the code runs great as long as all the worksheets are
visable. The problem comes in when one of the sheets is hidden.

I know this code is difficult to read. What I did was recorded the (20)
Macro's to do exactly what I wanted, then I moved them over to my User Form
Workbook and placed them in a module. That way all I have to do is just call
the Sub based on the Qty choosen in the Option Buttons.

I tried to space it apart so it is easier to see that each of these Subs
controls (20) Pages on (4) different Worksheets. I am sure there is a better
way to do this, but being new to this it was the only way i saw to do it.

Thanks for all your help and advice.

'************************************************************
'19 Battery String "Update Installer Forms"
'************************************************************
Sub Battery_String_19()

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
'--------------------------------------------------------------------
Sheets("Batt Chg Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:O912").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$O$912"
ActiveWindow.SmallScroll Down:=-12
Range("O3:O4").Select
ActiveCell.FormulaR1C1 = "19"
Range("O51:O52").Select
ActiveCell.FormulaR1C1 = "19"
Range("O99:O100").Select
ActiveCell.FormulaR1C1 = "19"
Range("O147:O148").Select
ActiveCell.FormulaR1C1 = "19"
Range("O195:O196").Select
ActiveCell.FormulaR1C1 = "19"
Range("O243:O244").Select
ActiveCell.FormulaR1C1 = "19"
Range("O291:O292").Select
ActiveCell.FormulaR1C1 = "19"
Range("O339:O340").Select
ActiveCell.FormulaR1C1 = "19"
Range("O387:O388").Select
ActiveCell.FormulaR1C1 = "19"
Range("O435:O436").Select
ActiveCell.FormulaR1C1 = "19"
Range("O483:O484").Select
ActiveCell.FormulaR1C1 = "19"
Range("O531:O532").Select
ActiveCell.FormulaR1C1 = "19"
Range("O579:O580").Select
ActiveCell.FormulaR1C1 = "19"
Range("O627:O628").Select
ActiveCell.FormulaR1C1 = "19"
Range("O675:O676").Select
ActiveCell.FormulaR1C1 = "19"
Range("O723:O724").Select
ActiveCell.FormulaR1C1 = "19"
Range("O771:O772").Select
ActiveCell.FormulaR1C1 = "19"
Range("O819:O820").Select
ActiveCell.FormulaR1C1 = "9"
Range("O819:O820").Select
ActiveCell.FormulaR1C1 = "19"
Range("O867:O868").Select
ActiveCell.FormulaR1C1 = "19"
Rows("913:960").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-16
Range("G6:H7").Select
'--------------------------------------------------------------------
Sheets("Pilot Cell Chg Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:G1273").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$1273"
ActiveWindow.LargeScroll Down:=22
ActiveWindow.SmallScroll Down:=-54
Rows("1274:1340").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-21
Range("B11:C11").Select
'--------------------------------------------------------------------
Sheets("Press Test Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:I1425").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$1425"
ActiveWindow.LargeScroll Down:=21
ActiveWindow.SmallScroll Down:=-18
Range("A1426:I1499").Select
ActiveWindow.SmallScroll Down:=-18
Rows("1426:1500").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-21
Range("B11:E11").Select
'--------------------------------------------------------------------
Sheets("Batt Strap Res Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:J1311").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$1311"
ActiveWindow.LargeScroll Down:=21
Rows("1312:1380").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-21
Range("H11:J11").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Install Pack Con").Select
End Sub

 

[Dave Peterson]

You could fix your code--making it easier to update later and easier to
understand right away--or you could add a couple of lines.

application.screenupdating = false

Will hide anything your code does from the user.

Then unhide your sheet, do the work, hide the sheet, and turn .screenupdating
back to true.

======
I think it's a better idea to clean up your code.

Sub Battery_String_19()
dim wks as worksheet

set wks = worksheets("Batt Chg Rpt")

with wks
.cells.EntireRow.Hidden = False
.pageSetup.PrintArea = "$A$1:$O$912"
.Range("O3:O4").value = 19
.Range("O51:O52").value = 19
.Range("O99:O100").value = 19
.Range("O147:O148").value = 19
.Range("O195:O196").value = 19
.Range("O243:O244").value = 19
.Range("O291:O292").value = 19
.Range("O339:O340").value = 19
.Range("O387:O388").value = 19

'etc

.Rows("913:960").EntireRow.Hidden = True
end with

'and so forth

 

[Brian]

Did you see that in the code Sub Battery_String_19() it is actually 4 marcos
combined?

Sheets("Batt Chg Rpt").Select
Sheets("Pilot Cell Chg Rpt").Select
Sheets("Press Test Rpt").Select
Sheets("Batt Strap Res Rpt").Select

I am not sure I understand what you mean by: or you could add a couple of
lines.
application.screenupdating = false

Will hide anything your code does from the user.

Then unhide your sheet, do the work, hide the sheet, and turn .screenupdating
back to true.

What would the code look like for the Sub to do what your suggesting?

Since this code is located in a module can I put the "Dim wks As Worksheet"
at the top as a module dim statement that applies to all the code? IS this
what the code would look like?

Sub Battery_String_19()
Set wks = Worksheets("Batt Chg Rpt")

With wks
.Cells.EntireRow.Hidden = False
.PageSetup.PrintArea = "$A$1:$O$912"
.Range("O3:O4").Value = 19
.Range("O51:O52").Value = 19
'etc
End With
'--------------------------------------------------------
Set wks = Worksheets("Pilot Cell Chg Rpt")
With wks

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:G1273").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$1273"
ActiveWindow.LargeScroll Down:=22
ActiveWindow.SmallScroll Down:=-54
Rows("1274:1340").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-21
Range("B11:C11").Select
End With
'--------------------------------------------------------
Set wks = Worksheets("Press Test Rpt")
With wks

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:I1425").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$1425"
ActiveWindow.LargeScroll Down:=21
ActiveWindow.SmallScroll Down:=-18
Range("A1426:I1499").Select
ActiveWindow.SmallScroll Down:=-18
Rows("1426:1500").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-21
Range("B11:E11").Select
End With
'--------------------------------------------------------
Set wks = WorksheetsBatt Strap Res Rpt")
With wks

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:J1311").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$1311"
ActiveWindow.LargeScroll Down:=21
Rows("1312:1380").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-21
Range("H11:J11").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Install Pack Con").Select
End With
End Sub

 

[Dave Peterson]

Yep. I saw that. I figured that if I helped with the first portion, you could
do the remaining.

And I mean that you could unhide a sheet without the user knowing that you unhid
it. By turning screenupdating off, the user won't see what your code does.
They'll be left in the dark.

==========
Notice how this is written:

Set wks = Worksheets("Batt Chg Rpt")
With wks
.Cells.EntireRow.Hidden = False
.PageSetup.PrintArea = "$A$1:$O$912"
.Range("O3:O4").Value = 19
.Range("O51:O52").Value = 19
'etc
End With

Each of those objects/properties that start with a dot refer back to the object
in the previous With statement.

So
..cells.entirerow.hidden = false
refers to the cells on the wks worksheet (which is the "Batt chg rpt" worksheet
in this case).

Your other code needs to be rewritten to avoid the .select's and has to qualify
each object/property you use.



Set wks = Worksheets("Pilot Cell Chg Rpt")
with wks
.cells.EntireRow.Hidden = False
.PageSetup.PrintArea = "$A$1:$G$1273"
.Rows("1274:1340").EntireRow.Hidden = True
End With

Notice the leading dots and the absense of .select's.

 

[Brian]

I am cleaning up the code now as you suggested. I like the way you shortened
it up. Kind of like the old saying "Less is more". The less code I have to
deal with, the more time I have other problems in this program.

How would I unhide it to update it like you suggest and then rehide it. I
also really want to turn the screen updating off as well. I have read that
that will improve performance too.

Here is the cleaner code: It works just like the old code did, just much
cleaner.

'************************************************************
'19 Battery String "Update Installer Forms"
'************************************************************
Sub Battery_String_19()

Set wks = Worksheets("Batt Chg Rpt")
With wks
.Cells.EntireRow.Hidden = False
.PageSetup.PrintArea = "$A$1:$O$912"
.Range("O3:O4").Value = 19
.Range("O51:O52").Value = 19
.Range("O99:O100").Value = 19
.Range("O147:O148").Value = 19
.Range("O195:O196").Value = 19
.Range("O243:O244").Value = 19
.Range("O291:O292").Value = 19
.Range("O339:O340").Value = 19
.Range("O387:O388").Value = 19
.Range("O435:O436").Value = 19
.Range("O483:O484").Value = 19
.Range("O531:O532").Value = 19
.Range("O579:O580").Value = 19
.Range("O627:O628").Value = 19
.Range("O675:O676").Value = 19
.Range("O723:O724").Value = 19
.Range("O771:O772").Value = 19
.Range("O819:O820").Value = 19
.Range("O819:O820").Value = 19
.Range("O867:O868").Value = 19
.Rows("913:960").EntireRow.Hidden = True
Range("G6:H7").Select
End With

Set wks = Worksheets("Pilot Cell Chg Rpt")
With wks
.Cells.EntireRow.Hidden = False
.PageSetup.PrintArea = "$A$1:$G$1273"
.Rows("1274:1340").EntireRow.Hidden = True
Range("B11:C11").Select
End With

Set wks = Worksheets("Press Test Rpt")
With wks
.Cells.EntireRow.Hidden = False
.PageSetup.PrintArea = "$A$1:$I$1425"
.Rows("1426:1500").EntireRow.Hidden = True
Range("B11:E11").Select
End With

Set wks = Worksheets("Batt Strap Res Rpt")
With wks
.Cells.EntireRow.Hidden = False
.PageSetup.PrintArea = "$A$1:$J$1311"
.Rows("1312:1380").EntireRow.Hidden = True
Range("H11:J11").Select
Sheets("Install Pack Con").Select
End With
End Sub

 

[Dave Peterson]

Since the new code is not selecting anything, the user won't see that flicking
and flashing.

But you could still use:

Sub yoursubnamehere()
application.screenupdating = false
'your code that does the work
application.screenupdating = true
end sub

If you wanted to unhide the sheet (you don't need to do it now with your
meaner/cleaner code):

sub yoursubnamehere()
dim wks as worksheet
application.screenupdating = false
set wks = worksheets("....")
with wks
.visible = xlsheetvisible
'a bunch of code
.visible = xlsheeethidden 'or xlsheetveryhidden
end with
application.screenupdating = true
end sub

 

[Brian]

I am going to post a new question on User form resizing, would you please
look at it and see if you may beable to help.