Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form Problem | Excel Programming | |||
Can I update User Form CheckMark without running _Click code...? | Excel Programming | |||
User form problem | Excel Worksheet Functions | |||
Help with a User Form problem please | Excel Programming | |||
user form problem | Excel Programming |