I have a problem I can't figure out, and I am seeking your help. I have a
macro that queries an Access database. This part of it runs fine the first
time. The second time it jumps to the combobox code and I can't figure out
why. (I noted where in jumps in the code below). I am not that
knowledgeable in programming and have muddled my way through so far with a
little help from a programmer in getting the code for the Access part, but
even in researching this newsgroup, I can't figure this out. Can you help me?

Thank You, Pam

Sub ProjectData2()
'Finds all data (Used to provide all project numbers to dropdown values)
Dim sWHERE As String
Dim sAccessFile As String
Dim sTable As String
Dim rowx As String
Dim filterrng As String
'Clears data in rows a-j in "Sample" worksheet
Sheets("Sample").Select
Range("A2:j2").Select
Selection.ClearContents

sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb"
sTable = "All Fields"

' Get rid of existing data in the table starting at row 5
ClearTableData2 5

' Construct a query using data in the table starting at row 2
ConstructQuery2 1, sWHERE

' Get query data from Access and put it in the table starting at row 5
QueryAccessToDataTable2 5, sAccessFile, sTable, sWHERE

'Filters data and updates dropdown values of project numbers
Worksheets("Sample").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
filterrng = Selection.Address
Range(filterrng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"BA6"), Unique:=True
Sheets("Dropdown Values").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents 'it jumps to my combo box
right after here
Sheets("Sample").Select
Range("bA6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Dropdown Values").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
rowx = Selection.End(xlDown).row
With ActiveWorkbook.Names("ProjectNo")
.Name = "ProjectNo"
.RefersToR1C1 = "='Dropdown Values'!R2C1:R" & rowx & "C1"
.Comment = ""
End With
Sheets("Estimate").Select
End Sub

I am going to guess that you have row source and/or controlsource linked to
the cells you are deleting from Sheets("Dropdown Values"). When you delete
the cells with the link, it defaults to the combobox with focus because you
no longer have a data source.

"Pam" wrote:

I have a problem I can't figure out, and I am seeking your help. I have a
macro that queries an Access database. This part of it runs fine the first
time. The second time it jumps to the combobox code and I can't figure out
why. (I noted where in jumps in the code below). I am not that
knowledgeable in programming and have muddled my way through so far with a
little help from a programmer in getting the code for the Access part, but
even in researching this newsgroup, I can't figure this out. Can you help me?

Thank You, Pam

Sub ProjectData2()
'Finds all data (Used to provide all project numbers to dropdown values)
Dim sWHERE As String
Dim sAccessFile As String
Dim sTable As String
Dim rowx As String
Dim filterrng As String
'Clears data in rows a-j in "Sample" worksheet
Sheets("Sample").Select
Range("A2:j2").Select
Selection.ClearContents

sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb"
sTable = "All Fields"

' Get rid of existing data in the table starting at row 5
ClearTableData2 5

' Construct a query using data in the table starting at row 2
ConstructQuery2 1, sWHERE

' Get query data from Access and put it in the table starting at row 5
QueryAccessToDataTable2 5, sAccessFile, sTable, sWHERE

'Filters data and updates dropdown values of project numbers
Worksheets("Sample").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
filterrng = Selection.Address
Range(filterrng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"BA6"), Unique:=True
Sheets("Dropdown Values").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents 'it jumps to my combo box
right after here
Sheets("Sample").Select
Range("bA6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Dropdown Values").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
rowx = Selection.End(xlDown).row
With ActiveWorkbook.Names("ProjectNo")
.Name = "ProjectNo"
.RefersToR1C1 = "='Dropdown Values'!R2C1:R" & rowx & "C1"
.Comment = ""
End With
Sheets("Estimate").Select
End Sub

Yes, you are right. In the properties box for the combobox, the RowSource is
the range name. I did it this way because the range can change. Can you
suggest a fix? Any help would be appreciated.

Thank You,

Pam

"JLGWhiz" wrote:

I am going to guess that you have row source and/or controlsource linked to
the cells you are deleting from Sheets("Dropdown Values"). When you delete
the cells with the link, it defaults to the combobox with focus because you
no longer have a data source.

"Pam" wrote:

I have a problem I can't figure out, and I am seeking your help. I have a
macro that queries an Access database. This part of it runs fine the first
time. The second time it jumps to the combobox code and I can't figure out
why. (I noted where in jumps in the code below). I am not that
knowledgeable in programming and have muddled my way through so far with a
little help from a programmer in getting the code for the Access part, but
even in researching this newsgroup, I can't figure this out. Can you help me?

Thank You, Pam

Sub ProjectData2()
'Finds all data (Used to provide all project numbers to dropdown values)
Dim sWHERE As String
Dim sAccessFile As String
Dim sTable As String
Dim rowx As String
Dim filterrng As String
'Clears data in rows a-j in "Sample" worksheet
Sheets("Sample").Select
Range("A2:j2").Select
Selection.ClearContents

sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb"
sTable = "All Fields"

' Get rid of existing data in the table starting at row 5
ClearTableData2 5

' Construct a query using data in the table starting at row 2
ConstructQuery2 1, sWHERE

' Get query data from Access and put it in the table starting at row 5
QueryAccessToDataTable2 5, sAccessFile, sTable, sWHERE

'Filters data and updates dropdown values of project numbers
Worksheets("Sample").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
filterrng = Selection.Address
Range(filterrng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"BA6"), Unique:=True
Sheets("Dropdown Values").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents 'it jumps to my combo box
right after here
Sheets("Sample").Select
Range("bA6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Dropdown Values").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
rowx = Selection.End(xlDown).row
With ActiveWorkbook.Names("ProjectNo")
.Name = "ProjectNo"
.RefersToR1C1 = "='Dropdown Values'!R2C1:R" & rowx & "C1"
.Comment = ""
End With
Sheets("Estimate").Select
End Sub

I tried changing my code so it just overrights what's in the named range.
When I start the macro the second time I can tell right away that it's not
going to work because the first form displays on the left side of the screen
instead of the center, and then my combo box has the last entry in it with a
blank drop down. What am I doing wrong?

Pam

"Pam" wrote:

Yes, you are right. In the properties box for the combobox, the RowSource is
the range name. I did it this way because the range can change. Can you
suggest a fix? Any help would be appreciated.

Thank You,

Pam

"JLGWhiz" wrote:

I am going to guess that you have row source and/or controlsource linked to
the cells you are deleting from Sheets("Dropdown Values"). When you delete
the cells with the link, it defaults to the combobox with focus because you
no longer have a data source.

"Pam" wrote:

I have a problem I can't figure out, and I am seeking your help. I have a
macro that queries an Access database. This part of it runs fine the first
time. The second time it jumps to the combobox code and I can't figure out
why. (I noted where in jumps in the code below). I am not that
knowledgeable in programming and have muddled my way through so far with a
little help from a programmer in getting the code for the Access part, but
even in researching this newsgroup, I can't figure this out. Can you help me?

Thank You, Pam

Sub ProjectData2()
'Finds all data (Used to provide all project numbers to dropdown values)
Dim sWHERE As String
Dim sAccessFile As String
Dim sTable As String
Dim rowx As String
Dim filterrng As String
'Clears data in rows a-j in "Sample" worksheet
Sheets("Sample").Select
Range("A2:j2").Select
Selection.ClearContents

sAccessFile = ThisWorkbook.Path & "\projectestimationdraft1.mdb"
sTable = "All Fields"

' Get rid of existing data in the table starting at row 5
ClearTableData2 5

' Construct a query using data in the table starting at row 2
ConstructQuery2 1, sWHERE

' Get query data from Access and put it in the table starting at row 5
QueryAccessToDataTable2 5, sAccessFile, sTable, sWHERE

'Filters data and updates dropdown values of project numbers
Worksheets("Sample").Select
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
filterrng = Selection.Address
Range(filterrng).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"BA6"), Unique:=True
Sheets("Dropdown Values").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents 'it jumps to my combo box
right after here
Sheets("Sample").Select
Range("bA6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Dropdown Values").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
rowx = Selection.End(xlDown).row
With ActiveWorkbook.Names("ProjectNo")
.Name = "ProjectNo"
.RefersToR1C1 = "='Dropdown Values'!R2C1:R" & rowx & "C1"
.Comment = ""
End With
Sheets("Estimate").Select
End Sub