Hi, could someone please explain to me what 'Control Source' does?

I've read the Help on it repeatedly, and it makes my head spin... I
just can't understand what it's trying to say.

I did think I had it figured and was using it... but just now found I
can REM it out with no effect.

Here's my usage...

List1.ControlSource = "Sheet1!P3"

If the number in P3 there was 6, what should that do?

Thanks - Kirk

The control source is used with a listbox (or similar control) so the data on
the worksheet and the listbox contain the same data. Here is the
explaination from the VBA help

The ControlSource property identifies a cell or field; it does not contain
the data stored in the cell or field. If you change the Value of the control,
the change is automatically reflected in the linked cell or field. Similarly,
if you change the value of the linked cell or field, the change is
automatically reflected in the Value of the control.



"kirkm" wrote:


Hi, could someone please explain to me what 'Control Source' does?

I've read the Help on it repeatedly, and it makes my head spin... I
just can't understand what it's trying to say.

I did think I had it figured and was using it... but just now found I
can REM it out with no effect.

Here's my usage...

List1.ControlSource = "Sheet1!P3"

If the number in P3 there was 6, what should that do?

Thanks - Kirk

If P3 has the value 6, then the value of the control will be 6. Let say you
have ListBox control and the ControlSource is P6 then the ListBox will have
one item with the value 6, if the ControlSource will be Sheet1!P3:P12 then
the list will have 10 items containing the values of P3:P12.

Hope this explains it.

"kirkm" wrote in message ...

Hi, could someone please explain to me what 'Control Source' does?

I've read the Help on it repeatedly, and it makes my head spin... I
just can't understand what it's trying to say.

I did think I had it figured and was using it... but just now found I
can REM it out with no effect.

Here's my usage...

List1.ControlSource = "Sheet1!P3"

If the number in P3 there was 6, what should that do?

Thanks - Kirk

On Wed, 3 Jun 2009 04:02:01 -0700, Joel
wrote:

The control source is used with a listbox (or similar control) so the data on
the worksheet and the listbox contain the same data. Here is the
explaination from the VBA help

The ControlSource property identifies a cell or field; it does not contain
the data stored in the cell or field. If you change the Value of the control,
the change is automatically reflected in the linked cell or field. Similarly,
if you change the value of the linked cell or field, the change is
automatically reflected in the Value of the control.

Sorry Joel, that's what I've read 1,000 times and makes no sense to
me.

Cheers - Kirk

On Wed, 3 Jun 2009 12:07:24 +0100, "Howard31"
wrote:

If P3 has the value 6, then the value of the control will be 6.

By 'value of the control' what do you mean? The list Box itself ?
Wouldn't that depend on the selected row ?


Let say you
have ListBox control and the ControlSource is P6 then the ListBox will have
one item with the value 6, if the ControlSource will be Sheet1!P3:P12 then
the list will have 10 items containing the values of P3:P12.

Hope this explains it.

Sorry, no... isn't thats whatRowSource does e.g.

frmTest.ListBox1.RowSource = "Test!A1:I5"

That seems to work for me... but I'm not sure of anything anymore :)

Although is something to do with the number of columns in a List Box?
I have 5 or 6 columns... also Control Source Help says CELL or FIELD
singular.

Cheers - Kirk

On Wed, 3 Jun 2009 12:07:24 +0100, "Howard31"
wrote:

If P3 has the value 6, then the value of the control will be 6.

By 'value of the control' what do you mean? The list Box itself ?
Wouldn't that depend on the selected row ?


Let say you
have ListBox control and the ControlSource is P6 then the ListBox will have
one item with the value 6, if the ControlSource will be Sheet1!P3:P12 then
the list will have 10 items containing the values of P3:P12.

Hope this explains it.

Sorry, no... isn't thats whatRowSource does e.g.

frmTest.ListBox1.RowSource = "Test!A1:I5"

That seems to work for me... but I'm not sure of anything anymore :)

Although is something to do with the number of columns in a List Box?
I have 5 or 6 columns... also Control Source Help says CELL or FIELD
singular.

Cheers - Kirk

Hi Kirk,

RowSource is as you say used to load a listbox with values. Control Source
will hold the selected value, and will also be the selected value next time
the userform is activated.

frmTest.ListBox1.RowSource = "Test!A1:I5"
frmTest.ListBox1.ControlSource="Test!K1"

Hopes this helps.

//Per


"kirkm" skrev i meddelelsen
...
On Wed, 3 Jun 2009 12:07:24 +0100, "Howard31"
wrote:

If P3 has the value 6, then the value of the control will be 6.

By 'value of the control' what do you mean? The list Box itself ?
Wouldn't that depend on the selected row ?


Let say you
have ListBox control and the ControlSource is P6 then the ListBox will
have
one item with the value 6, if the ControlSource will be Sheet1!P3:P12 then
the list will have 10 items containing the values of P3:P12.

Hope this explains it.

Sorry, no... isn't thats whatRowSource does e.g.

frmTest.ListBox1.RowSource = "Test!A1:I5"

That seems to work for me... but I'm not sure of anything anymore :)

Although is something to do with the number of columns in a List Box?
I have 5 or 6 columns... also Control Source Help says CELL or FIELD
singular.

Cheers - Kirk

Hi Kirk,

RowSource is as you say used to load a listbox with values. Control Source
will hold the selected value, and will also be the selected value next time
the userform is activated.

frmTest.ListBox1.RowSource = "Test!A1:I5"
frmTest.ListBox1.ControlSource="Test!K1"

Hopes this helps.

//Per


"kirkm" skrev i meddelelsen
...
On Wed, 3 Jun 2009 12:07:24 +0100, "Howard31"
wrote:

If P3 has the value 6, then the value of the control will be 6.

By 'value of the control' what do you mean? The list Box itself ?
Wouldn't that depend on the selected row ?


Let say you
have ListBox control and the ControlSource is P6 then the ListBox will
have
one item with the value 6, if the ControlSource will be Sheet1!P3:P12 then
the list will have 10 items containing the values of P3:P12.

Hope this explains it.

Sorry, no... isn't thats whatRowSource does e.g.

frmTest.ListBox1.RowSource = "Test!A1:I5"

That seems to work for me... but I'm not sure of anything anymore :)

Although is something to do with the number of columns in a List Box?
I have 5 or 6 columns... also Control Source Help says CELL or FIELD
singular.

Cheers - Kirk

On Wed, 3 Jun 2009 14:35:04 +0200, "Per Jessen"
wrote:

Hi Kirk,

RowSource is as you say used to load a listbox with values. Control Source
will hold the selected value, and will also be the selected value next time
the userform is activated.

frmTest.ListBox1.RowSource = "Test!A1:I5"
frmTest.ListBox1.ControlSource="Test!K1"

Hopes this helps.

Per, I'm not sure. The whole problem is twofold. I can't figure out
what ControlSource is or does. In your example above would is the
value in K1 and what is the result of with and without it?

If I knew that, it might shed some light on this.... I want to open
a Form+ ListBox with a selected line highlit. Most times it's
0 for line 1, but sometimes another line is wanted. This number is
variable mLine in the following code.

Call WriteCell("Test", mLine, "P3")
List1.ControlSource = "Test!P3"
List1.ListIndex = mLine
List1.Selected(mLine) = True
List1.BoundColumn = 0
DoEvents

It works reasonably often BUT every so often the wrong
line is highlit and isn't what mLine is. It's a right ******* because
if you put a STOP in the code to check values, it NEVER fails
to be correct. I've also just found a Sleeep 1000 (from
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
also seems to make it work.
So it seems some kind of delay is needed. Has anyone else struck
this ? Is the above code correct ?

Thanks - Kirk

On Wed, 3 Jun 2009 14:35:04 +0200, "Per Jessen"
wrote:

Hi Kirk,

RowSource is as you say used to load a listbox with values. Control Source
will hold the selected value, and will also be the selected value next time
the userform is activated.

frmTest.ListBox1.RowSource = "Test!A1:I5"
frmTest.ListBox1.ControlSource="Test!K1"

Hopes this helps.

Per, I'm not sure. The whole problem is twofold. I can't figure out
what ControlSource is or does. In your example above would is the
value in K1 and what is the result of with and without it?

If I knew that, it might shed some light on this.... I want to open
a Form+ ListBox with a selected line highlit. Most times it's
0 for line 1, but sometimes another line is wanted. This number is
variable mLine in the following code.

Call WriteCell("Test", mLine, "P3")
List1.ControlSource = "Test!P3"
List1.ListIndex = mLine
List1.Selected(mLine) = True
List1.BoundColumn = 0
DoEvents

It works reasonably often BUT every so often the wrong
line is highlit and isn't what mLine is. It's a right ******* because
if you put a STOP in the code to check values, it NEVER fails
to be correct. I've also just found a Sleeep 1000 (from
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
also seems to make it work.
So it seems some kind of delay is needed. Has anyone else struck
this ? Is the above code correct ?

Thanks - Kirk