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

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

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

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,

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

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

Dave,

Changing the status (enabled property) from true to true or false to false
is causing a Run-time error 1004 on RefreshAll, based on the code I using.

I moved the Cbo.LinkedCell to a different sheet. But I get the same error.

I also used code to populate the ListFillRange (rowsource) in the
cbo1_Change sub (see below). Again, I get the same error.

I don't want to change the list when the workbook opens. Because the user's
selection values must remain as saved in the Cbos. So when the user reopens
the "BabyCategory" workbook (for example) in a week or month. All they have
to do is RefreshAll to import the new sales data. W/out the need to reselect
the same hierarchy values (Dept, Cat, SubCat, Segment).

Option Explicit
Public blEnabled As Boolean
Private Sub cbo1_Change()
If blEnabled Then Exit Sub

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData
import
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll

Else

cbo2.ListFillRange = RowSrc
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll

End If
End Sub

--
Thanks again, Kevin


"Dave Peterson" wrote:

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_C lick€ÀšÃ‚Â� (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

Did you try moving the rowsource to a different sheet--not just the linked cell?

You could always save the current values of the controls to different cells (on
that hidden sheet) and put them back into the controls when the workbook is
reopened.

But I think I'm out of suggestions.

AFSSkier wrote:

Dave,

Changing the status (enabled property) from true to true or false to false
is causing a Run-time error 1004 on RefreshAll, based on the code I using.

I moved the Cbo.LinkedCell to a different sheet. But I get the same error.

I also used code to populate the ListFillRange (rowsource) in the
cbo1_Change sub (see below). Again, I get the same error.

I don't want to change the list when the workbook opens. Because the user's
selection values must remain as saved in the Cbos. So when the user reopens
the "BabyCategory" workbook (for example) in a week or month. All they have
to do is RefreshAll to import the new sales data. W/out the need to reselect
the same hierarchy values (Dept, Cat, SubCat, Segment).

Option Explicit
Public blEnabled As Boolean
Private Sub cbo1_Change()
If blEnabled Then Exit Sub

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData
import
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll

Else

cbo2.ListFillRange = RowSrc
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll

End If
End Sub

--
Thanks again, Kevin

"Dave Peterson" wrote:

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_C lick€ÀšÃ‚Â� (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


--

Dave Peterson

Dave,

The only place I can find a "RowSource" is in a User Form. I'm using Active
Control cbos and the rowsrc is a "ListFillRange".

I did try moving the ListFillRange (rowsource) and the LinkedCell value to a
different sheet. But I get the same error.

I know its not a good idea to use "On Error Resume Next"and "On Error GoTo
0". But the Enable Properties Run-time errors during the RefreshAll and Close
are resolved by them.

Now the only issue I'm having is, I'd like to refresh the QueryTables on a
hidden sheets w/out selecting them (*see code goes here). For Example, when
the sheet1 cbo1.ListCell.Value changes it fires a Refresh for inbound data
for cbo2.ListFillRange. The "Refresh Automatically When Cell Value Changes"
in the Connection Property/Parameters, works. But cbo2 is Enabled before the
QueryTable is completely refreshed. This is why I'd like to Refresh the
hidden sheet with code. Then Enable the cbo.

I recorded the following with the macro recorder. But when I copy it into
my code, I get a Run-time error 1004.
Sheets("CatData").Select
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

I was able to get this modified code to work. However when I hide the
QueryTable sheet, I get a Run-time error 1004. How do I refresh only a
single Hidden QueryTable?
When this code is hidden, I get a Run-time error 438.
With Sheets("CatData").Select
.querytable.refresh backgroundquery:=false
End With

Option Explicit
Private Sub cbo1_Change()

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData import

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

On Error Resume Next 'to resolve unnecessory Run-time errors
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll
On Error GoTo 0
Else
If Worksheets(1).Range("F7").Value = "0".Value Then

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

On Error Resume Next 'to resolve unnecessory Run-time errors
cboCat.Enabled = True 'Run-time error 1004 on RefreshAll
On Error GoTo 0
Else

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

cbo2.ListFillRange = RowSrc
On Error Resume Next 'to resolve unnecessory Run-time errors
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll
On Error GoTo 0

End If
End If
End Sub

Thank you for all of your efforts,
Kevin

"Dave Peterson" wrote:

Did you try moving the rowsource to a different sheet--not just the linked cell?

You could always save the current values of the controls to different cells (on
that hidden sheet) and put them back into the controls when the workbook is
reopened.

But I think I'm out of suggestions.

AFSSkier wrote:

Dave,

Changing the status (enabled property) from true to true or false to false
is causing a Run-time error 1004 on RefreshAll, based on the code I using.

I moved the Cbo.LinkedCell to a different sheet. But I get the same error.

I also used code to populate the ListFillRange (rowsource) in the
cbo1_Change sub (see below). Again, I get the same error.

I don't want to change the list when the workbook opens. Because the user's
selection values must remain as saved in the Cbos. So when the user reopens
the "BabyCategory" workbook (for example) in a week or month. All they have
to do is RefreshAll to import the new sales data. W/out the need to reselect
the same hierarchy values (Dept, Cat, SubCat, Segment).

Option Explicit
Public blEnabled As Boolean
Private Sub cbo1_Change()
If blEnabled Then Exit Sub

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData
import
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll

Else

cbo2.ListFillRange = RowSrc
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll

End If
End Sub

--
Thanks again, Kevin

"Dave Peterson" wrote:

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 meant ListFillRange. (They're essentially the same property.)

Try dropping the .select

With Sheets("CatData")
.querytable.refresh backgroundquery:=false
End With



AFSSkier wrote:

Dave,

The only place I can find a "RowSource" is in a User Form. I'm using Active
Control cbos and the rowsrc is a "ListFillRange".

I did try moving the ListFillRange (rowsource) and the LinkedCell value to a
different sheet. But I get the same error.

I know its not a good idea to use "On Error Resume Next"and "On Error GoTo
0". But the Enable Properties Run-time errors during the RefreshAll and Close
are resolved by them.

Now the only issue I'm having is, I'd like to refresh the QueryTables on a
hidden sheets w/out selecting them (*see code goes here). For Example, when
the sheet1 cbo1.ListCell.Value changes it fires a Refresh for inbound data
for cbo2.ListFillRange. The "Refresh Automatically When Cell Value Changes"
in the Connection Property/Parameters, works. But cbo2 is Enabled before the
QueryTable is completely refreshed. This is why I'd like to Refresh the
hidden sheet with code. Then Enable the cbo.

I recorded the following with the macro recorder. But when I copy it into
my code, I get a Run-time error 1004.
Sheets("CatData").Select
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

I was able to get this modified code to work. However when I hide the
QueryTable sheet, I get a Run-time error 1004. How do I refresh only a
single Hidden QueryTable?
When this code is hidden, I get a Run-time error 438.
With Sheets("CatData").Select
.querytable.refresh backgroundquery:=false
End With

Option Explicit
Private Sub cbo1_Change()

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData import

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

On Error Resume Next 'to resolve unnecessory Run-time errors
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll
On Error GoTo 0
Else
If Worksheets(1).Range("F7").Value = "0".Value Then

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

On Error Resume Next 'to resolve unnecessory Run-time errors
cboCat.Enabled = True 'Run-time error 1004 on RefreshAll
On Error GoTo 0
Else

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

cbo2.ListFillRange = RowSrc
On Error Resume Next 'to resolve unnecessory Run-time errors
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll
On Error GoTo 0

End If
End If
End Sub

Thank you for all of your efforts,
Kevin

"Dave Peterson" wrote:

Did you try moving the rowsource to a different sheet--not just the linked cell?

You could always save the current values of the controls to different cells (on
that hidden sheet) and put them back into the controls when the workbook is
reopened.

But I think I'm out of suggestions.

AFSSkier wrote:

Dave,

Changing the status (enabled property) from true to true or false to false
is causing a Run-time error 1004 on RefreshAll, based on the code I using.

I moved the Cbo.LinkedCell to a different sheet. But I get the same error.

I also used code to populate the ListFillRange (rowsource) in the
cbo1_Change sub (see below). Again, I get the same error.

I don't want to change the list when the workbook opens. Because the user's
selection values must remain as saved in the Cbos. So when the user reopens
the "BabyCategory" workbook (for example) in a week or month. All they have
to do is RefreshAll to import the new sales data. W/out the need to reselect
the same hierarchy values (Dept, Cat, SubCat, Segment).

Option Explicit
Public blEnabled As Boolean
Private Sub cbo1_Change()
If blEnabled Then Exit Sub

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData
import
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll

Else

cbo2.ListFillRange = RowSrc
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll

End If
End Sub

--
Thanks again, Kevin

"Dave Peterson" wrote:

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

--

Dave Peterson

Droping the .select, gives me an error 438 "Object doesn't support this
property or method".

With Sheets("CatData") 'no .select - Error 438
.QueryTable.Refresh BackgroundQuery:=False
End With

I also tried this, but get an error 91 "Object variable not set" at the
ListObject.QueryTable.Refresh.
Applicaton.ScreenUpdating = False
Sheets("CatData").Visible = True
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False 'Error 91
Sheets("CatData").Visible = False
Applicaton.ScreenUpdating = True

--
Thanks, Kevin


"Dave Peterson" wrote:

I meant ListFillRange. (They're essentially the same property.)

Try dropping the .select

With Sheets("CatData")
.querytable.refresh backgroundquery:=false
End With



AFSSkier wrote:

Dave,

The only place I can find a "RowSource" is in a User Form. I'm using Active
Control cbos and the rowsrc is a "ListFillRange".

I did try moving the ListFillRange (rowsource) and the LinkedCell value to a
different sheet. But I get the same error.

I know its not a good idea to use "On Error Resume Next"and "On Error GoTo
0". But the Enable Properties Run-time errors during the RefreshAll and Close
are resolved by them.

Now the only issue I'm having is, I'd like to refresh the QueryTables on a
hidden sheets w/out selecting them (*see code goes here). For Example, when
the sheet1 cbo1.ListCell.Value changes it fires a Refresh for inbound data
for cbo2.ListFillRange. The "Refresh Automatically When Cell Value Changes"
in the Connection Property/Parameters, works. But cbo2 is Enabled before the
QueryTable is completely refreshed. This is why I'd like to Refresh the
hidden sheet with code. Then Enable the cbo.

I recorded the following with the macro recorder. But when I copy it into
my code, I get a Run-time error 1004.
Sheets("CatData").Select
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

I was able to get this modified code to work. However when I hide the
QueryTable sheet, I get a Run-time error 1004. How do I refresh only a
single Hidden QueryTable?
When this code is hidden, I get a Run-time error 438.
With Sheets("CatData").Select
.querytable.refresh backgroundquery:=false
End With

Option Explicit
Private Sub cbo1_Change()

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData import

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

On Error Resume Next 'to resolve unnecessory Run-time errors
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll
On Error GoTo 0
Else
If Worksheets(1).Range("F7").Value = "0".Value Then

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

On Error Resume Next 'to resolve unnecessory Run-time errors
cboCat.Enabled = True 'Run-time error 1004 on RefreshAll
On Error GoTo 0
Else

'<== *Hidden Worksheets("CatData").QueryTable.Refresh code for cbo2 goes here

cbo2.ListFillRange = RowSrc
On Error Resume Next 'to resolve unnecessory Run-time errors
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll
On Error GoTo 0

End If
End If
End Sub

Thank you for all of your efforts,
Kevin

"Dave Peterson" wrote:

Did you try moving the rowsource to a different sheet--not just the linked cell?

You could always save the current values of the controls to different cells (on
that hidden sheet) and put them back into the controls when the workbook is
reopened.

But I think I'm out of suggestions.

AFSSkier wrote:

Dave,

Changing the status (enabled property) from true to true or false to false
is causing a Run-time error 1004 on RefreshAll, based on the code I using.

I moved the Cbo.LinkedCell to a different sheet. But I get the same error.

I also used code to populate the ListFillRange (rowsource) in the
cbo1_Change sub (see below). Again, I get the same error.

I don't want to change the list when the workbook opens. Because the user's
selection values must remain as saved in the Cbos. So when the user reopens
the "BabyCategory" workbook (for example) in a week or month. All they have
to do is RefreshAll to import the new sales data. W/out the need to reselect
the same hierarchy values (Dept, Cat, SubCat, Segment).

Option Explicit
Public blEnabled As Boolean
Private Sub cbo1_Change()
If blEnabled Then Exit Sub

Dim rng As Range, RowSrc As String
Set rng = ActiveWorkbook.Worksheets("CatData").Range("$A$1:$ D$126")
RowSrc = rng.Address(External:=True)

If cbo1.Value = "0" Then
cbo2.ListFillRange = "" 'reset cbo list to null/blank
cbo2.Value = "%" 'wild card to Requery All records for CatData
import
cbo2.Enabled = False 'Run-time error 1004 on RefreshAll
cbo3.Enabled = False 'Run-time error 1004 on RefreshAll
cbo4.Enabled = False 'Run-time error 1004 on RefreshAll

Else

cbo2.ListFillRange = RowSrc
cbo2.ListIndex = 0 'Run-time error 380 on close
cbo2.Enabled = True 'Run-time error 1004 on RefreshAll

End If
End Sub

--
Thanks again, Kevin

"Dave Peterson" wrote:

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