Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox.Enable giving error on ActiveWorkbook.RefreshAll
I have cascading Active ComboBoxes. In the code below I disable Cbo2 If
Cbo1.Value = "0" Else Enable. These CboBoxes are on a sheet, not a UserForm. In the code, when the Dropdown is opened it requeries Cbo2,3,4 to ListIndex = 0.I tried a Change sub with a Cbo.Enable = True/False to disable and enable the proceeding Active ComboBoxes. It works great for the Cascading Dropdowns. However Im getting a Run-time error 1004 on the Enable = True/False, from my CmdButton sub CmdRefreshAll_Click (see 2nd sub below). It has an ActiveWorkbook.RefreshAll. I even tried reversing the If in the Cbo. Private Sub Cbo1_Change() If Cbo1.Value = "0" Then Cbo2.ListIndex = 0 Cbo3.ListIndex = 0 Cbo4.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = False 'Run-time error 1004 Cbo3.Enabled = False 'Run-time error 1004 Cbo4.Enabled = False 'Run-time error 1004 Else Cbo2.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets Application.ScreenUpdating = False I was hoping this would resolve issues in change sub Application.EnableEvents = False ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page Sheets(1).Select Range("H1").Value = "Refresh All Date: " Range("H2").Value = Now Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub -- Thanks, Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox.Enable giving error on ActiveWorkbook.RefreshAll
First, I couldn't get it to break.
But application.enableevents won't stop the combobox/commandbuttons from firing. I'd use this in the _click event: Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub Those unqualified ranges don't belong to sheets(1)--they belong to the sheet owning the code (and I'm assuming that sheets(1) isn't that sheet. Now come my guesses... I'd qualify all the objects in your code: Option Explicit Dim BlkProc as boolean Private Sub Cbo1_Change() if blkproc = true then exit sub end if If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub I'm not sure how this will help, but it can't hurt. My next question is do you have linked cells for any of those comboboxes (especially cbo1) on that same sheet? Maybe the .refreshall is causing the cbo1_change event to fire and that's screwing things up. If you delete the linked cell (or even move it to another sheet), do you still have trouble? If you do, try this: At the top of this module, add this line: dim BlkProc as boolean So your final code would look like: Option Explicit dim BlkProc as boolean Private Sub Cbo1_Change() If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing blkproc = true ActiveWorkbook.RefreshAll blkproc = false 'if it doesn't help here, move it right before End sub 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub The blkproc variable is just a home grown version of "application.enableevents". AFSSkier wrote: I have cascading Active ComboBoxes. In the code below I disable Cbo2 If Cbo1.Value = "0" Else Enable. These CboBoxes are on a sheet, not a UserForm. In the code, when the Dropdown is opened it requeries Cbo2,3,4 to ListIndex = 0.I tried a Change sub with a Cbo.Enable = True/False to disable and enable the proceeding Active ComboBoxes. It works great for the Cascading Dropdowns. However Im getting a Run-time error 1004 on the Enable = True/False, from my CmdButton sub CmdRefreshAll_Click (see 2nd sub below). It has an ActiveWorkbook.RefreshAll. I even tried reversing the If in the Cbo. Private Sub Cbo1_Change() If Cbo1.Value = "0" Then Cbo2.ListIndex = 0 Cbo3.ListIndex = 0 Cbo4.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = False 'Run-time error 1004 Cbo3.Enabled = False 'Run-time error 1004 Cbo4.Enabled = False 'Run-time error 1004 Else Cbo2.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets Application.ScreenUpdating = False I was hoping this would resolve issues in change sub Application.EnableEvents = False ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page Sheets(1).Select Range("H1").Value = "Refresh All Date: " Range("H2").Value = Now Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub -- Thanks, Kevin -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox.Enable giving error on ActiveWorkbook.RefreshAll
Dave,
I moved the LinkedCells to another sheet & I get the same Run-time error 1004. I also tried all of your suggestions with the same result. When I step through the Click code for the RefreshAll, I don't get the error. Because the code stops at the ClickEnd. But when I run the Click code, it runs through the change code, too. When it comes to the like Enabled = False, it says "The cbobox is already disabled - Error 1004". The same goes for the Enabled = True "The cbobox is already enabled - Error 1004". Let me give you the full history of what I'm trying to do. I started with Cascading CboBoxes on an Access Form that provided an Excel export. I've moved the same process to a Refreshable Excel data Import. I'm trying to duplicate the Access Form on an Excel sheet with Cascading Active CboBoxes. Each CboLinkedCell fires a Refresh inbound data for the proceeding CboBox. For example, cbo1 fires a Refresh of Datasheet2. cbo2's ListFillRange Datasheet2, etc. etc. Each CboBox has is on Datasheet. That the DataQuery uses the CboLinkCell as the filter for the Cascading effect. These CboBoxes are from a hierarchy. cbo1 = DEPARTMENT (Groc) cbo2 = CATEGORY (Baby/Infant) cbo3 = SUBCAT (Infant Formula) cbo4 = SEGMENT (Powdered) If you look at the Access Form Code below, you can probably get an idea what I'm trying to do. Private Sub Cbo1_AfterUpdate() 'This will requery ComboBoxes 2 and 3 Me.Cbo2.Requery Me.Cbo3.Requery End Sub For Cbo2 Data, Row Source Type Table/Query, Row Source: SELECT DISTINCT [my_table].[field2] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1])) ORDER BY [my_table].[field2]; For Cbo3: SELECT DISTINCT [my_table].[field3] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1]) AND (([my_table].[field2]) Like [Forms]![my_form]![Cbo2])) ORDER BY [my_table].[field3]; Private Sub Cbo2_AfterUpdate() 'This will requery Combo Box 3 Me.Cbo3.Requery End Sub -- Thank you for helping me with this, Kevin "Dave Peterson" wrote: First, I couldn't get it to break. But application.enableevents won't stop the combobox/commandbuttons from firing. I'd use this in the _click event: Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub Those unqualified ranges don't belong to sheets(1)--they belong to the sheet owning the code (and I'm assuming that sheets(1) isn't that sheet. Now come my guesses... I'd qualify all the objects in your code: Option Explicit Dim BlkProc as boolean Private Sub Cbo1_Change() if blkproc = true then exit sub end if If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub I'm not sure how this will help, but it can't hurt. My next question is do you have linked cells for any of those comboboxes (especially cbo1) on that same sheet? Maybe the .refreshall is causing the cbo1_change event to fire and that's screwing things up. If you delete the linked cell (or even move it to another sheet), do you still have trouble? If you do, try this: At the top of this module, add this line: dim BlkProc as boolean So your final code would look like: Option Explicit dim BlkProc as boolean Private Sub Cbo1_Change() If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing blkproc = true ActiveWorkbook.RefreshAll blkproc = false 'if it doesn't help here, move it right before End sub 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub The blkproc variable is just a home grown version of "application.enableevents". AFSSkier wrote: I have cascading Active ComboBoxes. In the code below I disable Cbo2 If Cbo1.Value = "0" Else Enable. These CboBoxes are on a sheet, not a UserForm. In the code, when the Dropdown is opened it requeries Cbo2,3,4 to ListIndex = 0.I tried a Change sub with a Cbo.Enable = True/False to disable and enable the proceeding Active ComboBoxes. It works great for the Cascading Dropdowns. However Iâm getting a âœRun-time error 1004â on the Enable = True/False, from my CmdButton sub âœCmdRefreshAll_Clickâ (see 2nd sub below). It has an ActiveWorkbook.RefreshAll. I even tried reversing the If in the Cbo. Private Sub Cbo1_Change() If Cbo1.Value = "0" Then Cbo2.ListIndex = 0 Cbo3.ListIndex = 0 Cbo4.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = False 'Run-time error 1004 Cbo3.Enabled = False 'Run-time error 1004 Cbo4.Enabled = False 'Run-time error 1004 Else Cbo2.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets Application.ScreenUpdating = False â˜I was hoping this would resolve issues in change sub Application.EnableEvents = False ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page Sheets(1).Select Range("H1").Value = "Refresh All Date: " Range("H2").Value = Now Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub -- Thanks, Kevin -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox.Enable giving error on ActiveWorkbook.RefreshAll
I don't have any more suggestions.
If you remove the linked cells completely does the problem go away? Maybe you can just populate the contents of those cells in code. And you tried the version with the blkproc variable in it??? Just wondering. AFSSkier wrote: Dave, I moved the LinkedCells to another sheet & I get the same Run-time error 1004. I also tried all of your suggestions with the same result. When I step through the Click code for the RefreshAll, I don't get the error. Because the code stops at the ClickEnd. But when I run the Click code, it runs through the change code, too. When it comes to the like Enabled = False, it says "The cbobox is already disabled - Error 1004". The same goes for the Enabled = True "The cbobox is already enabled - Error 1004". Let me give you the full history of what I'm trying to do. I started with Cascading CboBoxes on an Access Form that provided an Excel export. I've moved the same process to a Refreshable Excel data Import. I'm trying to duplicate the Access Form on an Excel sheet with Cascading Active CboBoxes. Each CboLinkedCell fires a Refresh inbound data for the proceeding CboBox. For example, cbo1 fires a Refresh of Datasheet2. cbo2's ListFillRange Datasheet2, etc. etc. Each CboBox has is on Datasheet. That the DataQuery uses the CboLinkCell as the filter for the Cascading effect. These CboBoxes are from a hierarchy. cbo1 = DEPARTMENT (Groc) cbo2 = CATEGORY (Baby/Infant) cbo3 = SUBCAT (Infant Formula) cbo4 = SEGMENT (Powdered) If you look at the Access Form Code below, you can probably get an idea what I'm trying to do. Private Sub Cbo1_AfterUpdate() 'This will requery ComboBoxes 2 and 3 Me.Cbo2.Requery Me.Cbo3.Requery End Sub For Cbo2 Data, Row Source Type Table/Query, Row Source: SELECT DISTINCT [my_table].[field2] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1])) ORDER BY [my_table].[field2]; For Cbo3: SELECT DISTINCT [my_table].[field3] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1]) AND (([my_table].[field2]) Like [Forms]![my_form]![Cbo2])) ORDER BY [my_table].[field3]; Private Sub Cbo2_AfterUpdate() 'This will requery Combo Box 3 Me.Cbo3.Requery End Sub -- Thank you for helping me with this, Kevin "Dave Peterson" wrote: First, I couldn't get it to break. But application.enableevents won't stop the combobox/commandbuttons from firing. I'd use this in the _click event: Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub Those unqualified ranges don't belong to sheets(1)--they belong to the sheet owning the code (and I'm assuming that sheets(1) isn't that sheet. Now come my guesses... I'd qualify all the objects in your code: Option Explicit Dim BlkProc as boolean Private Sub Cbo1_Change() if blkproc = true then exit sub end if If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub I'm not sure how this will help, but it can't hurt. My next question is do you have linked cells for any of those comboboxes (especially cbo1) on that same sheet? Maybe the .refreshall is causing the cbo1_change event to fire and that's screwing things up. If you delete the linked cell (or even move it to another sheet), do you still have trouble? If you do, try this: At the top of this module, add this line: dim BlkProc as boolean So your final code would look like: Option Explicit dim BlkProc as boolean Private Sub Cbo1_Change() If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing blkproc = true ActiveWorkbook.RefreshAll blkproc = false 'if it doesn't help here, move it right before End sub 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub The blkproc variable is just a home grown version of "application.enableevents". AFSSkier wrote: I have cascading Active ComboBoxes. In the code below I disable Cbo2 If Cbo1.Value = "0" Else Enable. These CboBoxes are on a sheet, not a UserForm. In the code, when the Dropdown is opened it requeries Cbo2,3,4 to ListIndex = 0.I tried a Change sub with a Cbo.Enable = True/False to disable and enable the proceeding Active ComboBoxes. It works great for the Cascading Dropdowns. However Iâm getting a âœRun-time error 1004â on the Enable = True/False, from my CmdButton sub âœCmdRefreshAll_Clickâ (see 2nd sub below). It has an ActiveWorkbook.RefreshAll. I even tried reversing the If in the Cbo. Private Sub Cbo1_Change() If Cbo1.Value = "0" Then Cbo2.ListIndex = 0 Cbo3.ListIndex = 0 Cbo4.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = False 'Run-time error 1004 Cbo3.Enabled = False 'Run-time error 1004 Cbo4.Enabled = False 'Run-time error 1004 Else Cbo2.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets Application.ScreenUpdating = False â˜I was hoping this would resolve issues in change sub Application.EnableEvents = False ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page Sheets(1).Select Range("H1").Value = "Refresh All Date: " Range("H2").Value = Now Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub -- Thanks, Kevin -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox.Enable giving error on ActiveWorkbook.RefreshAll
Dave,
Yes, the form is on sheet1. But will soon be in the #2 sheet(2) position. As you suggested, I removed the linked cells and still get the Run-time error 1004 on RefreshAll. I seams to error when the code come back into the CboBox sub on RefreshAll. When it dumps to the cbobox.Enabled = True, it says "why do you want this enabled when it already is?" But, when it hits the cbobox.Enabled = False, it says "why do you want this dinabled when it already is?". However when I disconnect the connect of the import source data for each of the hierarchy sheets, the RefreshAll does not give me any errors. -- Thanks again, Kevin "Dave Peterson" wrote: I don't have any more suggestions. If you remove the linked cells completely does the problem go away? Maybe you can just populate the contents of those cells in code. And you tried the version with the blkproc variable in it??? Just wondering. AFSSkier wrote: Dave, I moved the LinkedCells to another sheet & I get the same Run-time error 1004. I also tried all of your suggestions with the same result. When I step through the Click code for the RefreshAll, I don't get the error. Because the code stops at the ClickEnd. But when I run the Click code, it runs through the change code, too. When it comes to the like Enabled = False, it says "The cbobox is already disabled - Error 1004". The same goes for the Enabled = True "The cbobox is already enabled - Error 1004". Let me give you the full history of what I'm trying to do. I started with Cascading CboBoxes on an Access Form that provided an Excel export. I've moved the same process to a Refreshable Excel data Import. I'm trying to duplicate the Access Form on an Excel sheet with Cascading Active CboBoxes. Each CboLinkedCell fires a Refresh inbound data for the proceeding CboBox. For example, cbo1 fires a Refresh of Datasheet2. cbo2's ListFillRange Datasheet2, etc. etc. Each CboBox has is on Datasheet. That the DataQuery uses the CboLinkCell as the filter for the Cascading effect. These CboBoxes are from a hierarchy. cbo1 = DEPARTMENT (Groc) cbo2 = CATEGORY (Baby/Infant) cbo3 = SUBCAT (Infant Formula) cbo4 = SEGMENT (Powdered) If you look at the Access Form Code below, you can probably get an idea what I'm trying to do. Private Sub Cbo1_AfterUpdate() 'This will requery ComboBoxes 2 and 3 Me.Cbo2.Requery Me.Cbo3.Requery End Sub For Cbo2 Data, Row Source Type Table/Query, Row Source: SELECT DISTINCT [my_table].[field2] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1])) ORDER BY [my_table].[field2]; For Cbo3: SELECT DISTINCT [my_table].[field3] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1]) AND (([my_table].[field2]) Like [Forms]![my_form]![Cbo2])) ORDER BY [my_table].[field3]; Private Sub Cbo2_AfterUpdate() 'This will requery Combo Box 3 Me.Cbo3.Requery End Sub -- Thank you for helping me with this, Kevin "Dave Peterson" wrote: First, I couldn't get it to break. But application.enableevents won't stop the combobox/commandbuttons from firing. I'd use this in the _click event: Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub Those unqualified ranges don't belong to sheets(1)--they belong to the sheet owning the code (and I'm assuming that sheets(1) isn't that sheet. Now come my guesses... I'd qualify all the objects in your code: Option Explicit Dim BlkProc as boolean Private Sub Cbo1_Change() if blkproc = true then exit sub end if If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub I'm not sure how this will help, but it can't hurt. My next question is do you have linked cells for any of those comboboxes (especially cbo1) on that same sheet? Maybe the .refreshall is causing the cbo1_change event to fire and that's screwing things up. If you delete the linked cell (or even move it to another sheet), do you still have trouble? If you do, try this: At the top of this module, add this line: dim BlkProc as boolean So your final code would look like: Option Explicit dim BlkProc as boolean Private Sub Cbo1_Change() If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing blkproc = true ActiveWorkbook.RefreshAll blkproc = false 'if it doesn't help here, move it right before End sub 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub The blkproc variable is just a home grown version of "application.enableevents". AFSSkier wrote: I have cascading Active ComboBoxes. In the code below I disable Cbo2 If Cbo1.Value = "0" Else Enable. These CboBoxes are on a sheet, not a UserForm. In the code, when the Dropdown is opened it requeries Cbo2,3,4 to ListIndex = 0.I tried a Change sub with a Cbo.Enable = True/False to disable and enable the proceeding Active ComboBoxes. It works great for the Cascading Dropdowns. However Iââ¬â¢m getting a ââ¬ÅRun-time error 1004ââ¬Â on the Enable = True/False, from my CmdButton sub ââ¬ÅCmdRefreshAll_Clickââ¬Â (see 2nd sub below). It has an ActiveWorkbook.RefreshAll. I even tried reversing the If in the Cbo. Private Sub Cbo1_Change() If Cbo1.Value = "0" Then Cbo2.ListIndex = 0 Cbo3.ListIndex = 0 Cbo4.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = False 'Run-time error 1004 Cbo3.Enabled = False 'Run-time error 1004 Cbo4.Enabled = False 'Run-time error 1004 Else Cbo2.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets Application.ScreenUpdating = False ââ¬ËœI was hoping this would resolve issues in change sub Application.EnableEvents = False ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page Sheets(1).Select Range("H1").Value = "Refresh All Date: " Range("H2").Value = Now Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub -- Thanks, Kevin -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox.Enable giving error on ActiveWorkbook.RefreshAll
Changing the status (.enabled property) from true to true or false to false
won't, er, shouldn't cause any trouble. Earlier you wrote that you move the linked cells to different sheets and still had the trouble. Have you tried moving the .rowsource to a different sheet? (I don't recall if you did.) But if you did (or will) and you have trouble, why not just populate the ..rowsource in code--either when the workbook opens or the worksheet is activated or whenever you think it's a good idea? AFSSkier wrote: Dave, Yes, the form is on sheet1. But will soon be in the #2 sheet(2) position. As you suggested, I removed the linked cells and still get the Run-time error 1004 on RefreshAll. I seams to error when the code come back into the CboBox sub on RefreshAll. When it dumps to the cbobox.Enabled = True, it says "why do you want this enabled when it already is?" But, when it hits the cbobox.Enabled = False, it says "why do you want this dinabled when it already is?". However when I disconnect the connect of the import source data for each of the hierarchy sheets, the RefreshAll does not give me any errors. -- Thanks again, Kevin "Dave Peterson" wrote: I don't have any more suggestions. If you remove the linked cells completely does the problem go away? Maybe you can just populate the contents of those cells in code. And you tried the version with the blkproc variable in it??? Just wondering. AFSSkier wrote: Dave, I moved the LinkedCells to another sheet & I get the same Run-time error 1004. I also tried all of your suggestions with the same result. When I step through the Click code for the RefreshAll, I don't get the error. Because the code stops at the ClickEnd. But when I run the Click code, it runs through the change code, too. When it comes to the like Enabled = False, it says "The cbobox is already disabled - Error 1004". The same goes for the Enabled = True "The cbobox is already enabled - Error 1004". Let me give you the full history of what I'm trying to do. I started with Cascading CboBoxes on an Access Form that provided an Excel export. I've moved the same process to a Refreshable Excel data Import. I'm trying to duplicate the Access Form on an Excel sheet with Cascading Active CboBoxes. Each CboLinkedCell fires a Refresh inbound data for the proceeding CboBox. For example, cbo1 fires a Refresh of Datasheet2. cbo2's ListFillRange Datasheet2, etc. etc. Each CboBox has is on Datasheet. That the DataQuery uses the CboLinkCell as the filter for the Cascading effect. These CboBoxes are from a hierarchy. cbo1 = DEPARTMENT (Groc) cbo2 = CATEGORY (Baby/Infant) cbo3 = SUBCAT (Infant Formula) cbo4 = SEGMENT (Powdered) If you look at the Access Form Code below, you can probably get an idea what I'm trying to do. Private Sub Cbo1_AfterUpdate() 'This will requery ComboBoxes 2 and 3 Me.Cbo2.Requery Me.Cbo3.Requery End Sub For Cbo2 Data, Row Source Type Table/Query, Row Source: SELECT DISTINCT [my_table].[field2] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1])) ORDER BY [my_table].[field2]; For Cbo3: SELECT DISTINCT [my_table].[field3] FROM [my_table] WHERE ((([my_table].[field1]) Like [Forms]![my_form]![Cbo1]) AND (([my_table].[field2]) Like [Forms]![my_form]![Cbo2])) ORDER BY [my_table].[field3]; Private Sub Cbo2_AfterUpdate() 'This will requery Combo Box 3 Me.Cbo3.Requery End Sub -- Thank you for helping me with this, Kevin "Dave Peterson" wrote: First, I couldn't get it to break. But application.enableevents won't stop the combobox/commandbuttons from firing. I'd use this in the _click event: Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub Those unqualified ranges don't belong to sheets(1)--they belong to the sheet owning the code (and I'm assuming that sheets(1) isn't that sheet. Now come my guesses... I'd qualify all the objects in your code: Option Explicit Dim BlkProc as boolean Private Sub Cbo1_Change() if blkproc = true then exit sub end if If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub I'm not sure how this will help, but it can't hurt. My next question is do you have linked cells for any of those comboboxes (especially cbo1) on that same sheet? Maybe the .refreshall is causing the cbo1_change event to fire and that's screwing things up. If you delete the linked cell (or even move it to another sheet), do you still have trouble? If you do, try this: At the top of this module, add this line: dim BlkProc as boolean So your final code would look like: Option Explicit dim BlkProc as boolean Private Sub Cbo1_Change() If me.cbo1.Value = "0" Then me.cbo2.ListIndex = 0 me.cbo3.ListIndex = 0 me.cbo4.ListIndex = 0 me.cbo2.Enabled = False 'Run-time error 1004 me.cbo3.Enabled = False 'Run-time error 1004 me.cbo4.Enabled = False 'Run-time error 1004 Else me.cbo2.ListIndex = 0 me.cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets 'Application.ScreenUpdating = False 'turned off for testing blkproc = true ActiveWorkbook.RefreshAll blkproc = false 'if it doesn't help here, move it right before End sub 'Add RefreshAll date & time to Form page with worksheets(1) .Range("H1").Value = "Refresh All Date: " .Range("H2").Value = Now end with Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub The blkproc variable is just a home grown version of "application.enableevents". AFSSkier wrote: I have cascading Active ComboBoxes. In the code below I disable Cbo2 If Cbo1.Value = "0" Else Enable. These CboBoxes are on a sheet, not a UserForm. In the code, when the Dropdown is opened it requeries Cbo2,3,4 to ListIndex = 0.I tried a Change sub with a Cbo.Enable = True/False to disable and enable the proceeding Active ComboBoxes. It works great for the Cascading Dropdowns. However Iââ¬â¢m getting a ââ¬ÅRun-time error 1004ââ¬Â on the Enable = True/False, from my CmdButton sub ââ¬ÅCmdRefreshAll_Clickââ¬Â (see 2nd sub below). It has an ActiveWorkbook.RefreshAll. I even tried reversing the If in the Cbo. Private Sub Cbo1_Change() If Cbo1.Value = "0" Then Cbo2.ListIndex = 0 Cbo3.ListIndex = 0 Cbo4.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = False 'Run-time error 1004 Cbo3.Enabled = False 'Run-time error 1004 Cbo4.Enabled = False 'Run-time error 1004 Else Cbo2.ListIndex = 0 'Run-time error 1004 on ActiveWorkbook.RefreshAll (see CmdRefreshAll sub) Cbo2.Enabled = True 'Run-time error 1004 End If End Sub Private Sub CmdRefreshAll_Click() 'Refresh data sheets from outside data sources & Pivotsheets Application.ScreenUpdating = False ââ¬ËœI was hoping this would resolve issues in change sub Application.EnableEvents = False ActiveWorkbook.RefreshAll 'Add RefreshAll date & time to Form page Sheets(1).Select Range("H1").Value = "Refresh All Date: " Range("H2").Value = Now Application.ScreenUpdating = True MsgBox "All Data Sheets & Pivot Tables in this Workbook are updated" End Sub -- Thanks, Kevin -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveWorkbook.RefreshAll | Excel Discussion (Misc queries) | |||
ActiveWorkbook.RefreshAll | Excel Programming | |||
ActiveWorkbook.RefreshAll | Excel Programming | |||
ActiveWorkbook.RefreshAll | Excel Discussion (Misc queries) | |||
ActiveWorkbook.RefreshAll | Excel Programming |