Controlsource errors

[Mats Samson]

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

 

[William Benson]

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]

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]

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

 

[William Benson]

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

See Chip Pearson's :

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

 

[Mats Samson]

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]

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]

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