Microsoft Office Tutorials and References
In Depth Information
On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
cc.Text = Query.Text
End If
End Sub
Notice that saving the query in this fashion triggers the OnAction event associated with this
control, which is the EnterDatabaseQuery routine located in the ThisWorkbook module,
which is shown here. This routine scans the list of queries stored in the control and appends
the new query to the end if it isn’t found, letting users rerun a particular query quickly.
Sub EnterDatabaseQuery()
Dim c As CommandBar
Dim cc As CommandBarComboBox
DimqAsString
DimiAsLong
On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Query")
Set cc = c.FindControl(, , "Excel2k3 VBA Query Statement")
If Not cc Is Nothing Then
q = cc.Text
i=1
Do While i <= cc.ListCount
If q = cc.List(i) Then
Exit Sub
End If
i=i+1
Loop
cc.AddItem cc.Text
End If
This routine begins by locating the CommandBarComboBox control, and then saves the value
from the Te xt property into a temporary variable named q. Next the routine uses a Do…While
loop to scan through the list of queries stored in the drop-down list. If a match is found, the
Exit Sub statement ends the routine without action. If no match is found, the AddItem
method is used to add the query to the end of the list of drop-down items.
Search JabSto ::




Custom Search