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 said:
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
:
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
:
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
:
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
