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