Good morning Dave,

Thank you very much for the detail explanation.

If I may, I would like to ask you a question on querytables. I have been
looking for the answer to that question, and I have not had any luck.

From the projects that I work on, I connect an Excel file to an Access
database to retrieve data. For the connection, I do not use code, instead I
connect them through Data Import External Data New Database Query. Part
of the connection setup, I give a querytable name and check the box "Save
query definition" in the External Data Range Properties. If I want to delete
the querytable that I created, I uncheck the Save Query Definition box on the
properties and delete the define name of that querytable. After I uncheck
and delete the define name of that querytable, when I recreate a new
querytable using the same querytable name that I just deleted, I would not
able to use it. I receive an error message that the name is already exist on
that sheet.

I thought I deleted, and why would the querytable name still exist? Is
there a way to delete the querytables permanently so that when I want to use
the same name again, I would be able to use it?

Thanks.

"Dave Peterson" wrote:

It tells excel's VBA that you want to be forced to declare all the variables you
use.

That way, if you do something like:

Dim ctr1 as long
ctr1 = ctrl + 1

You won't have to spend hours finding the typo (1 vs l (one vs ell)).

You'd get a compile error right away.

Souny wrote:

Dave,

I have a quick question. What is the purpose of using the "Option
Explicit"? Thanks.

"Dave Peterson" wrote:

Hope it works for you.

Souny wrote:

Dave,

Thanks for the explanation. I am using Excel 2003 Standard and Windows XP
Profession.

Have a great weekend.

By the way, thanks for your helps on the message "Need Help on Define Names"
that I posted on 09/19/09. I apologize for not able to respond to your
message.

"Dave Peterson" wrote:

Somethings cause xl to recalculate and think that that linked cell (or list)
changed (I'm not sure which one).

I'm not sure what they are or when they do it (what version of excel are you
using?).

Just a couple of days ago, there was another user who had the same problem. I
couldn't duplicate it xl2003 (which is what they were using, too(!)).

All I know is that sometimes it's just easier to use code than to struggle.

Souny wrote:

Dave,

Thank you very much for the code. I will try it and let you know.

One thing that does not make sense to me. Why do we have to go through the
code to populate the values, and why my setup would not work?

I thought the intend of the combo box is to execute the code when we click
on the combo box and when the value changes in the combo box, not to execute
automatically when the file opens. Am I correct?

Did I miss something during the setup that you can think of?

Thanks again.

"Dave Peterson" wrote:

Say you have the list in a range on a separate sheet named "ListInfo" in A1:A10.
And you want the linked cell on the same worksheet as the combobox in A1.

And you want to populate the combobox when the workbook opens.

My combobox was named combobox1 and is on Sheet1.

I had this code in a General module:

Option Explicit
Sub Auto_Open()
Dim wks As Worksheet
Dim ListRng As Range

Set wks = Worksheets("Sheet1")

With Worksheets("ListInfo")
Set ListRng = .Range("A1:A5")
End With

wks.OLEObjects("combobox1").Object.List = ListRng.Value
End Sub

Behind the worksheet with the combobox:
Option Explicit
Private Sub ComboBox1_Change()
Me.Range("A1").Value = Me.ComboBox1.Value
End Sub

========
I have no idea how often that combobox needs to have its list refreshed. Maybe
using a different event would make more sense.


Souny wrote:

Dave,

Thanks for your response. Could you help me with the code?

"Dave Peterson" wrote:

You may want to just bite the bullet and drop the linkedcell and rowsource and
replace them with code.

Souny wrote:

Tom,

Could you help me one thing?

I have a combo box in one of my worksheets, and the code for the combo box
is also in the same worksheet "Sheet1". Basically, the code will execute the
lines based on the value selected (from clicking) from the combo box.
Somehow, the code automatically executes when the file is opened.

I have tried many ways, and I am still having problems. I don't understand
why the code will automatically execute when the file is opened.

Below is my code structure.

Private Sub cboCode_Click()

Application.ScreenUpdating=False

Select Case cboCode.Value
Case "Selection1"
'Do the following
Case "Selection2"
'Do the following
Cash "Selection3"
'Do the following
Case Else
'Do the following
End Select

Application.ScreenUpdating = True
End Sub

Please help.

Thanks.

"Tom Ogilvy" wrote:

I depends on what you think the normal rules are.

right click on the combobox and select properties. in rowsource, the link
for the dropdown list, put something like

Sheet1!A1:A30

in Control source (the link for the result)
sheet2!B9

--
Regards,
Tom Ogilvy


"Andy the yeti" wrote:

Hi,

Due to some superb help from these groups I have almost achieved what I
wanted to do using a userform, I have one last hurdle now that involves using
a combo box on a userform.

Do these follow the rules of normal of combo boxes in regards to a cell link
and input range? If so could I have some pointers please on how to set up the
properties?

Thank you very much
Andy


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson