Hello,
Ive several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get the error
380: €œCould not set the ControlSource property. Invalid property value.€�
It doesnt matter how Im referencing to the cell in ControlSource, with a
variable, with the cell Name, with the cell range, the error pops anyway.
It seems like any value or string in the controlsource cell triggers the
error.
It happens to OptionButtons as well. A True or False value in the cell €¦Poff!

But as said, it was working before! Does ControlSource get €œcontaminated€�
with usage? I tried with VBACleaner as well but it didnt help very much. I
got other strange errors instead. (I could get the form to work if I opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didnt work + other strange errors like not loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") €˜ Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range(€œA4€�)
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = €œDelTime€�
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? Its very problematic to have empty cells as other
formulas are depending on an index value set by f.i. the ListBox. Errors in
these formulas triggers €œType Mismatch€� instead.

Can anybody come up with a good solution?
Regards
Mats

This line is completely not going to work: TextBox8.ControlSource =
"DelTime"

Also, the control source should be a string which represents the range, not
the range itself. This you could know by the fact that you do not use "SET"
in assigning it.

TextBox5.ControlSource = ("'" & MD.Name &"'!A4")
TextBox6.ControlSource = RecOrder1.Address

Finally, I suggest DIMMING your variables as ranges when they are ranges,
not using the shortcut to DIM them as variants.



"Mats Samson" wrote in message
...
Hello,
I've several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get the
error
380: "Could not set the ControlSource property. Invalid property value."
It doesn't matter how I'm referencing to the cell in ControlSource, with a
variable, with the cell Name, with the cell range, the error pops anyway.
It seems like any value or string in the controlsource cell triggers the
error.
It happens to OptionButtons as well. A True or False value in the
cell .Poff!

But as said, it was working before! Does ControlSource get "contaminated"
with usage? I tried with VBACleaner as well but it didn't help very much.
I
got other strange errors instead. (I could get the form to work if I
opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didn't work + other strange errors like not loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") ' Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range("A4")
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? It's very problematic to have empty cells as other
formulas are depending on an index value set by f.i. the ListBox. Errors
in
these formulas triggers "Type Mismatch" instead.

Can anybody come up with a good solution?
Regards
Mats

Hi William,
well, it does! Try the following simple Form:

Private Sub UserForm_Initialize()
' Worksheets("Testing").Activate
TextBox1.ControlSource = "B2"
TextBox2.ControlSource = "Hello"
End Sub

I Named cell B4 to Hello and wrote something in both B2 and B4 and it works
nicely.
I can write in the Textboxes and it changes the cells after Enter. If it is
within the same Workbook, it works also for the Named cell B4 without
activating the Testing worksheet.
(Its the line TextBox6.ControlSource = RecOrder thats not working.)
BUT, it doesnt work anymore in my Registration UserForm! AND, this Test
workbook doesnt work if my Registration workbook is loaded!!! I have no
viruses, that Ive checked, several times. It seems that €œgarbage€� is
contaminating the UserForm (or the entire workbook), preventing it from
working properly.
But I dont know how to make a proper cleaning without €œde-mounting€� my
entire workbook piece by piece. I'm afraid of using VBACleaner again, there's
no real control to what the program is changing and no logs to show what was
removed.
I still have problem with auto-loading my startup files and the link it had
to my Registration workbook is gone. Reestablishing it doesn't help for next
session.

"William Benson" wrote:

This line is completely not going to work: TextBox8.ControlSource =
"DelTime"

Also, the control source should be a string which represents the range, not
the range itself. This you could know by the fact that you do not use "SET"
in assigning it.

TextBox5.ControlSource = ("'" & MD.Name &"'!A4")
TextBox6.ControlSource = RecOrder1.Address

Finally, I suggest DIMMING your variables as ranges when they are ranges,
not using the shortcut to DIM them as variants.



"Mats Samson" wrote in message
...
Hello,
I've several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get the
error
380: "Could not set the ControlSource property. Invalid property value."
It doesn't matter how I'm referencing to the cell in ControlSource, with a
variable, with the cell Name, with the cell range, the error pops anyway.
It seems like any value or string in the controlsource cell triggers the
error.
It happens to OptionButtons as well. A True or False value in the
cell .Poff!

But as said, it was working before! Does ControlSource get "contaminated"
with usage? I tried with VBACleaner as well but it didn't help very much.
I
got other strange errors instead. (I could get the form to work if I
opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didn't work + other strange errors like not loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") ' Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range("A4")
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? It's very problematic to have empty cells as other
formulas are depending on an index value set by f.i. the ListBox. Errors
in
these formulas triggers "Type Mismatch" instead.

Can anybody come up with a good solution?
Regards
Mats

Sorry, I missed DelTime among the ranges set.

As I said, the ControlSource property is expecting either a name of a range,
or a cell address, and cannot be set to an object such as a Range object.
You may think you are setting it equal to the range, in actual fact you are
setting it equal (when Excel agrees it follows appropriate syntax) to the
TEXT property of the range, which is the default property. That is a common
VB / VBA theme. Same idea for fields in recordsets.

You can use this expression: TextBox6.ControlSource = RecOrder only if the
value in RecOrder is equating to a cell's address or another range.

When you write TextBox6.ControlSource = RecOrder you are really assigning
the default property of the range, not the range. And the default property
is the text. Here's how you should change your code and I am pretty sure it
should always work

(except I am having a problem with setting the controlsource of the Listbox
because it only works when the range that CompIX refers to is cleared to
begin with)

Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys")
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = "DocSys!A4"
TextBox6.ControlSource = "RecOrder"
TextBox7.ControlSource = "RecDate"
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = "WHAT"
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = "CompIX"
.BoundColumn = 0
End With
End Sub



If you have a range named Hello then that will work
"Mats Samson" wrote in message
...
Hi William,
well, it does! Try the following simple Form:

Private Sub UserForm_Initialize()
' Worksheets("Testing").Activate
TextBox1.ControlSource = "B2"
TextBox2.ControlSource = "Hello"
End Sub

I Named cell B4 to Hello and wrote something in both B2 and B4 and it
works
nicely.
I can write in the Textboxes and it changes the cells after Enter. If it
is
within the same Workbook, it works also for the Named cell B4 without
activating the Testing worksheet.
(It's the line TextBox6.ControlSource = RecOrder that's not working.)
BUT, it doesn't work anymore in my Registration UserForm! AND, this Test
workbook doesn't work if my Registration workbook is loaded!!! I have no
viruses, that I've checked, several times. It seems that "garbage" is
contaminating the UserForm (or the entire workbook), preventing it from
working properly.
But I don't know how to make a proper cleaning without "de-mounting" my
entire workbook piece by piece. I'm afraid of using VBACleaner again,
there's
no real control to what the program is changing and no logs to show what
was
removed.
I still have problem with auto-loading my startup files and the link it
had
to my Registration workbook is gone. Reestablishing it doesn't help for
next
session.

"William Benson" wrote:

This line is completely not going to work: TextBox8.ControlSource
=
"DelTime"

Also, the control source should be a string which represents the range,
not
the range itself. This you could know by the fact that you do not use
"SET"
in assigning it.

TextBox5.ControlSource = ("'" & MD.Name &"'!A4")
TextBox6.ControlSource = RecOrder1.Address

Finally, I suggest DIMMING your variables as ranges when they are ranges,
not using the shortcut to DIM them as variants.



"Mats Samson" wrote in message
...
Hello,
I've several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get the
error
380: "Could not set the ControlSource property. Invalid property
value."
It doesn't matter how I'm referencing to the cell in ControlSource,
with a
variable, with the cell Name, with the cell range, the error pops
anyway.
It seems like any value or string in the controlsource cell triggers
the
error.
It happens to OptionButtons as well. A True or False value in the
cell .Poff!

But as said, it was working before! Does ControlSource get
"contaminated"
with usage? I tried with VBACleaner as well but it didn't help very
much.
I
got other strange errors instead. (I could get the form to work if I
opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didn't work + other strange errors like not
loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") ' Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range("A4")
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? It's very problematic to have empty cells as other
formulas are depending on an index value set by f.i. the ListBox.
Errors
in
these formulas triggers "Type Mismatch" instead.

Can anybody come up with a good solution?
Regards
Mats

My error, the default property of the range object is the Value property.

See Chip Pearson's :

http://www.cpearson.com/excel/DefaultProperty.htm


"William Benson" wrote in message
...
Sorry, I missed DelTime among the ranges set.

As I said, the ControlSource property is expecting either a name of a
range, or a cell address, and cannot be set to an object such as a Range
object. You may think you are setting it equal to the range, in actual
fact you are setting it equal (when Excel agrees it follows appropriate
syntax) to the TEXT property of the range, which is the default property.
That is a common VB / VBA theme. Same idea for fields in recordsets.

You can use this expression: TextBox6.ControlSource = RecOrder only if
the value in RecOrder is equating to a cell's address or another range.

When you write TextBox6.ControlSource = RecOrder you are really assigning
the default property of the range, not the range. And the default property
is the text. Here's how you should change your code and I am pretty sure
it should always work

(except I am having a problem with setting the controlsource of the
Listbox because it only works when the range that CompIX refers to is
cleared to begin with)

Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys")
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = "DocSys!A4"
TextBox6.ControlSource = "RecOrder"
TextBox7.ControlSource = "RecDate"
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = "WHAT"
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = "CompIX"
.BoundColumn = 0
End With
End Sub



If you have a range named Hello then that will work
"Mats Samson" wrote in message
...
Hi William,
well, it does! Try the following simple Form:

Private Sub UserForm_Initialize()
' Worksheets("Testing").Activate
TextBox1.ControlSource = "B2"
TextBox2.ControlSource = "Hello"
End Sub

I Named cell B4 to Hello and wrote something in both B2 and B4 and it
works
nicely.
I can write in the Textboxes and it changes the cells after Enter. If it
is
within the same Workbook, it works also for the Named cell B4 without
activating the Testing worksheet.
(It's the line TextBox6.ControlSource = RecOrder that's not working.)
BUT, it doesn't work anymore in my Registration UserForm! AND, this Test
workbook doesn't work if my Registration workbook is loaded!!! I have no
viruses, that I've checked, several times. It seems that "garbage" is
contaminating the UserForm (or the entire workbook), preventing it from
working properly.
But I don't know how to make a proper cleaning without "de-mounting" my
entire workbook piece by piece. I'm afraid of using VBACleaner again,
there's
no real control to what the program is changing and no logs to show what
was
removed.
I still have problem with auto-loading my startup files and the link it
had
to my Registration workbook is gone. Reestablishing it doesn't help for
next
session.

"William Benson" wrote:

This line is completely not going to work: TextBox8.ControlSource
=
"DelTime"

Also, the control source should be a string which represents the range,
not
the range itself. This you could know by the fact that you do not use
"SET"
in assigning it.

TextBox5.ControlSource = ("'" & MD.Name &"'!A4")
TextBox6.ControlSource = RecOrder1.Address

Finally, I suggest DIMMING your variables as ranges when they are
ranges,
not using the shortcut to DIM them as variants.



"Mats Samson" wrote in message
...
Hello,
I've several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get the
error
380: "Could not set the ControlSource property. Invalid property
value."
It doesn't matter how I'm referencing to the cell in ControlSource,
with a
variable, with the cell Name, with the cell range, the error pops
anyway.
It seems like any value or string in the controlsource cell triggers
the
error.
It happens to OptionButtons as well. A True or False value in the
cell .Poff!

But as said, it was working before! Does ControlSource get
"contaminated"
with usage? I tried with VBACleaner as well but it didn't help very
much.
I
got other strange errors instead. (I could get the form to work if I
opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didn't work + other strange errors like not
loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") ' Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range("A4")
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? It's very problematic to have empty cells as other
formulas are depending on an index value set by f.i. the ListBox.
Errors
in
these formulas triggers "Type Mismatch" instead.

Can anybody come up with a good solution?
Regards
Mats

Hi William,
sorry for not responding. I was not working with it for some days and after
some changes I works again, but, the basic problem remains. Spite your
explanation, I'm still confused and not really sure about the vb logic in
this case.
I wanted to use variables named identically to Named ranges in my sheet
because you can move the location of the information without changing the vba
code. A straight logic between the sheet and the code. But it seems that vba
is sensitive to changing the location of a Named cell as well. Even if you
change the structure of the code the error occurs, f.i. I renamed the order
(number/name) of the textboxes so they follow the taborder and again the
error popped.
Surely it has to do with making the mistakes you mention, but sometimes vba
logic is confusing. F.i. with an ACTIVE sheet is the cell-reference "A7"
ALWAYS enough
or do you SOMETIMES have to provide Worksheet("BOOK1").Range("A7"). I'm not
sure! Caption seems to need Worksheets("BOOK1").Range("A7"), while
Controlsource can stay with "A7". Or?
Firstly, if I still want to use abbrevations, I need to change the
variablesnames to so it's clear if the refer to a range or a value (RecordRng
and RecordVal).

Best regards
Mats


"William Benson" wrote:

My error, the default property of the range object is the Value property.

See Chip Pearson's :

http://www.cpearson.com/excel/DefaultProperty.htm


"William Benson" wrote in message
...
Sorry, I missed DelTime among the ranges set.

As I said, the ControlSource property is expecting either a name of a
range, or a cell address, and cannot be set to an object such as a Range
object. You may think you are setting it equal to the range, in actual
fact you are setting it equal (when Excel agrees it follows appropriate
syntax) to the TEXT property of the range, which is the default property.
That is a common VB / VBA theme. Same idea for fields in recordsets.

You can use this expression: TextBox6.ControlSource = RecOrder only if
the value in RecOrder is equating to a cell's address or another range.

When you write TextBox6.ControlSource = RecOrder you are really assigning
the default property of the range, not the range. And the default property
is the text. Here's how you should change your code and I am pretty sure
it should always work

(except I am having a problem with setting the controlsource of the
Listbox because it only works when the range that CompIX refers to is
cleared to begin with)

Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys")
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = "DocSys!A4"
TextBox6.ControlSource = "RecOrder"
TextBox7.ControlSource = "RecDate"
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = "WHAT"
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = "CompIX"
.BoundColumn = 0
End With
End Sub



If you have a range named Hello then that will work
"Mats Samson" wrote in message
...
Hi William,
well, it does! Try the following simple Form:

Private Sub UserForm_Initialize()
' Worksheets("Testing").Activate
TextBox1.ControlSource = "B2"
TextBox2.ControlSource = "Hello"
End Sub

I Named cell B4 to Hello and wrote something in both B2 and B4 and it
works
nicely.
I can write in the Textboxes and it changes the cells after Enter. If it
is
within the same Workbook, it works also for the Named cell B4 without
activating the Testing worksheet.
(It's the line TextBox6.ControlSource = RecOrder that's not working.)
BUT, it doesn't work anymore in my Registration UserForm! AND, this Test
workbook doesn't work if my Registration workbook is loaded!!! I have no
viruses, that I've checked, several times. It seems that "garbage" is
contaminating the UserForm (or the entire workbook), preventing it from
working properly.
But I don't know how to make a proper cleaning without "de-mounting" my
entire workbook piece by piece. I'm afraid of using VBACleaner again,
there's
no real control to what the program is changing and no logs to show what
was
removed.
I still have problem with auto-loading my startup files and the link it
had
to my Registration workbook is gone. Reestablishing it doesn't help for
next
session.

"William Benson" wrote:

This line is completely not going to work: TextBox8.ControlSource
=
"DelTime"

Also, the control source should be a string which represents the range,
not
the range itself. This you could know by the fact that you do not use
"SET"
in assigning it.

TextBox5.ControlSource = ("'" & MD.Name &"'!A4")
TextBox6.ControlSource = RecOrder1.Address

Finally, I suggest DIMMING your variables as ranges when they are
ranges,
not using the shortcut to DIM them as variants.



"Mats Samson" wrote in message
...
Hello,
I've several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get the
error
380: "Could not set the ControlSource property. Invalid property
value."
It doesn't matter how I'm referencing to the cell in ControlSource,
with a
variable, with the cell Name, with the cell range, the error pops
anyway.
It seems like any value or string in the controlsource cell triggers
the
error.
It happens to OptionButtons as well. A True or False value in the
cell .Poff!

But as said, it was working before! Does ControlSource get
"contaminated"
with usage? I tried with VBACleaner as well but it didn't help very
much.
I
got other strange errors instead. (I could get the form to work if I
opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didn't work + other strange errors like not
loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") ' Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range("A4")
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? It's very problematic to have empty cells as other
formulas are depending on an index value set by f.i. the ListBox.
Errors
in
these formulas triggers "Type Mismatch" instead.

Can anybody come up with a good solution?
Regards
Mats

You have my e-mail, if you want to send the file and discuss it further I
can, but it is hard to infer from your writings what particular difficulty
you are facing. Or start fresh with a new post and maybe some of the real
gurus here can help.

Cheers.


"Mats Samson" wrote in message
...
Hi William,
sorry for not responding. I was not working with it for some days and
after
some changes I works again, but, the basic problem remains. Spite your
explanation, I'm still confused and not really sure about the vb logic in
this case.
I wanted to use variables named identically to Named ranges in my sheet
because you can move the location of the information without changing the
vba
code. A straight logic between the sheet and the code. But it seems that
vba
is sensitive to changing the location of a Named cell as well. Even if you
change the structure of the code the error occurs, f.i. I renamed the
order
(number/name) of the textboxes so they follow the taborder and again the
error popped.
Surely it has to do with making the mistakes you mention, but sometimes
vba
logic is confusing. F.i. with an ACTIVE sheet is the cell-reference "A7"
ALWAYS enough
or do you SOMETIMES have to provide Worksheet("BOOK1").Range("A7"). I'm
not
sure! Caption seems to need Worksheets("BOOK1").Range("A7"), while
Controlsource can stay with "A7". Or?
Firstly, if I still want to use abbrevations, I need to change the
variablesnames to so it's clear if the refer to a range or a value
(RecordRng
and RecordVal).

Best regards
Mats


"William Benson" wrote:

My error, the default property of the range object is the Value property.

See Chip Pearson's :

http://www.cpearson.com/excel/DefaultProperty.htm


"William Benson" wrote in message
...
Sorry, I missed DelTime among the ranges set.

As I said, the ControlSource property is expecting either a name of a
range, or a cell address, and cannot be set to an object such as a
Range
object. You may think you are setting it equal to the range, in actual
fact you are setting it equal (when Excel agrees it follows appropriate
syntax) to the TEXT property of the range, which is the default
property.
That is a common VB / VBA theme. Same idea for fields in recordsets.

You can use this expression: TextBox6.ControlSource = RecOrder only if
the value in RecOrder is equating to a cell's address or another range.

When you write TextBox6.ControlSource = RecOrder you are really
assigning
the default property of the range, not the range. And the default
property
is the text. Here's how you should change your code and I am pretty
sure
it should always work

(except I am having a problem with setting the controlsource of the
Listbox because it only works when the range that CompIX refers to is
cleared to begin with)

Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys")
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = "DocSys!A4"
TextBox6.ControlSource = "RecOrder"
TextBox7.ControlSource = "RecDate"
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = "WHAT"
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = "CompIX"
.BoundColumn = 0
End With
End Sub



If you have a range named Hello then that will work
"Mats Samson" wrote in message
...
Hi William,
well, it does! Try the following simple Form:

Private Sub UserForm_Initialize()
' Worksheets("Testing").Activate
TextBox1.ControlSource = "B2"
TextBox2.ControlSource = "Hello"
End Sub

I Named cell B4 to Hello and wrote something in both B2 and B4 and it
works
nicely.
I can write in the Textboxes and it changes the cells after Enter. If
it
is
within the same Workbook, it works also for the Named cell B4 without
activating the Testing worksheet.
(It's the line TextBox6.ControlSource = RecOrder that's not working.)
BUT, it doesn't work anymore in my Registration UserForm! AND, this
Test
workbook doesn't work if my Registration workbook is loaded!!! I have
no
viruses, that I've checked, several times. It seems that "garbage" is
contaminating the UserForm (or the entire workbook), preventing it
from
working properly.
But I don't know how to make a proper cleaning without "de-mounting"
my
entire workbook piece by piece. I'm afraid of using VBACleaner again,
there's
no real control to what the program is changing and no logs to show
what
was
removed.
I still have problem with auto-loading my startup files and the link
it
had
to my Registration workbook is gone. Reestablishing it doesn't help
for
next
session.

"William Benson" wrote:

This line is completely not going to work:
TextBox8.ControlSource
=
"DelTime"

Also, the control source should be a string which represents the
range,
not
the range itself. This you could know by the fact that you do not use
"SET"
in assigning it.

TextBox5.ControlSource = ("'" & MD.Name &"'!A4")
TextBox6.ControlSource = RecOrder1.Address

Finally, I suggest DIMMING your variables as ranges when they are
ranges,
not using the shortcut to DIM them as variants.



"Mats Samson" wrote in message
...
Hello,
I've several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get
the
error
380: "Could not set the ControlSource property. Invalid property
value."
It doesn't matter how I'm referencing to the cell in ControlSource,
with a
variable, with the cell Name, with the cell range, the error pops
anyway.
It seems like any value or string in the controlsource cell
triggers
the
error.
It happens to OptionButtons as well. A True or False value in the
cell .Poff!

But as said, it was working before! Does ControlSource get
"contaminated"
with usage? I tried with VBACleaner as well but it didn't help very
much.
I
got other strange errors instead. (I could get the form to work if
I
opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didn't work + other strange errors like not
loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") ' Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range("A4")
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? It's very problematic to have empty cells as
other
formulas are depending on an index value set by f.i. the ListBox.
Errors
in
these formulas triggers "Type Mismatch" instead.

Can anybody come up with a good solution?
Regards
Mats

Thanks William,
I'll check it a little bit further and then eventually come back!
Hasta la vista
Mats

"William Benson" wrote:

You have my e-mail, if you want to send the file and discuss it further I
can, but it is hard to infer from your writings what particular difficulty
you are facing. Or start fresh with a new post and maybe some of the real
gurus here can help.

Cheers.


"Mats Samson" wrote in message
...
Hi William,
sorry for not responding. I was not working with it for some days and
after
some changes I works again, but, the basic problem remains. Spite your
explanation, I'm still confused and not really sure about the vb logic in
this case.
I wanted to use variables named identically to Named ranges in my sheet
because you can move the location of the information without changing the
vba
code. A straight logic between the sheet and the code. But it seems that
vba
is sensitive to changing the location of a Named cell as well. Even if you
change the structure of the code the error occurs, f.i. I renamed the
order
(number/name) of the textboxes so they follow the taborder and again the
error popped.
Surely it has to do with making the mistakes you mention, but sometimes
vba
logic is confusing. F.i. with an ACTIVE sheet is the cell-reference "A7"
ALWAYS enough
or do you SOMETIMES have to provide Worksheet("BOOK1").Range("A7"). I'm
not
sure! Caption seems to need Worksheets("BOOK1").Range("A7"), while
Controlsource can stay with "A7". Or?
Firstly, if I still want to use abbrevations, I need to change the
variablesnames to so it's clear if the refer to a range or a value
(RecordRng
and RecordVal).

Best regards
Mats


"William Benson" wrote:

My error, the default property of the range object is the Value property.

See Chip Pearson's :

http://www.cpearson.com/excel/DefaultProperty.htm


"William Benson" wrote in message
...
Sorry, I missed DelTime among the ranges set.

As I said, the ControlSource property is expecting either a name of a
range, or a cell address, and cannot be set to an object such as a
Range
object. You may think you are setting it equal to the range, in actual
fact you are setting it equal (when Excel agrees it follows appropriate
syntax) to the TEXT property of the range, which is the default
property.
That is a common VB / VBA theme. Same idea for fields in recordsets.

You can use this expression: TextBox6.ControlSource = RecOrder only if
the value in RecOrder is equating to a cell's address or another range.

When you write TextBox6.ControlSource = RecOrder you are really
assigning
the default property of the range, not the range. And the default
property
is the text. Here's how you should change your code and I am pretty
sure
it should always work

(except I am having a problem with setting the controlsource of the
Listbox because it only works when the range that CompIX refers to is
cleared to begin with)

Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys")
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = "DocSys!A4"
TextBox6.ControlSource = "RecOrder"
TextBox7.ControlSource = "RecDate"
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = "WHAT"
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = "CompIX"
.BoundColumn = 0
End With
End Sub



If you have a range named Hello then that will work
"Mats Samson" wrote in message
...
Hi William,
well, it does! Try the following simple Form:

Private Sub UserForm_Initialize()
' Worksheets("Testing").Activate
TextBox1.ControlSource = "B2"
TextBox2.ControlSource = "Hello"
End Sub

I Named cell B4 to Hello and wrote something in both B2 and B4 and it
works
nicely.
I can write in the Textboxes and it changes the cells after Enter. If
it
is
within the same Workbook, it works also for the Named cell B4 without
activating the Testing worksheet.
(It's the line TextBox6.ControlSource = RecOrder that's not working.)
BUT, it doesn't work anymore in my Registration UserForm! AND, this
Test
workbook doesn't work if my Registration workbook is loaded!!! I have
no
viruses, that I've checked, several times. It seems that "garbage" is
contaminating the UserForm (or the entire workbook), preventing it
from
working properly.
But I don't know how to make a proper cleaning without "de-mounting"
my
entire workbook piece by piece. I'm afraid of using VBACleaner again,
there's
no real control to what the program is changing and no logs to show
what
was
removed.
I still have problem with auto-loading my startup files and the link
it
had
to my Registration workbook is gone. Reestablishing it doesn't help
for
next
session.

"William Benson" wrote:

This line is completely not going to work:
TextBox8.ControlSource
=
"DelTime"

Also, the control source should be a string which represents the
range,
not
the range itself. This you could know by the fact that you do not use
"SET"
in assigning it.

TextBox5.ControlSource = ("'" & MD.Name &"'!A4")
TextBox6.ControlSource = RecOrder1.Address

Finally, I suggest DIMMING your variables as ranges when they are
ranges,
not using the shortcut to DIM them as variants.



"Mats Samson" wrote in message
...
Hello,
I've several TextBoxes and ListBoxes linked to named cells in my
registration form.
Everything worked fine but suddenly when initiating the form I get
the
error
380: "Could not set the ControlSource property. Invalid property
value."
It doesn't matter how I'm referencing to the cell in ControlSource,
with a
variable, with the cell Name, with the cell range, the error pops
anyway.
It seems like any value or string in the controlsource cell
triggers
the
error.
It happens to OptionButtons as well. A True or False value in the
cell .Poff!

But as said, it was working before! Does ControlSource get
"contaminated"
with usage? I tried with VBACleaner as well but it didn't help very
much.
I
got other strange errors instead. (I could get the form to work if
I
opened
the workbook from within Excel, but if I started Excel with the
workbook-shortcut it didn't work + other strange errors like not
loading
startup files.)

A part of the code looks like:

Dim MD, RecOrder, RecDate, DelTime, CompIX, WHAT
Public Sub Userform_Initialize()
Sheets("DocSys").Activate
Set MD = Worksheets("DocSys") ' Main Document
Set CompIX = MD.Range("CompIX")
Set WHAT = MD.Range("WHAT")
Set RecOrder = MD.Range("RecOrder")
Set RecDate = MD.Range("RecDate")
Set RecDate = MD.Range("DelTime")

TextBox5.ControlSource = MD.Range("A4")
TextBox6.ControlSource = RecOrder
TextBox7.ControlSource = MD.Range("RecDate")
TextBox8.ControlSource = "DelTime"
OptionButton1.ControlSource = WHAT
With ListBox1
.ColumnCount = 2
.ColumnWidths = "0;72"
.RowSource = "Database"
.ControlSource = CompIX
.BoundColumn = 0
End With

What has happened? It's very problematic to have empty cells as
other
formulas are depending on an index value set by f.i. the ListBox.
Errors
in
these formulas triggers "Type Mismatch" instead.

Can anybody come up with a good solution?
Regards
Mats