Problem running sub on a User Form
 

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

Problem running sub on a User Form
 

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



Brian wrote:

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

Problem running sub on a User Form
 

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" wrote:

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