Controlsource Puzzlement

[Francis Knight]

Procedures I am required to follow in my job are often
presented on Excel Worksheets. To make it easier to follow
from a distance of a few feet, I wish to display the tasks
one-by-one on a UserForm in XL2000.

I had previously succeeded in Access, but it became too
tedious massaging and importing the source data, which could
be superceded unpredictably, so it seemed easier for the
future to operate on the primary data source.

My code and UserForm is in a separate Workbook, which I
intend to convert to an Add-In to share with my colleagues. I
have set the Controlsource of a number of TextBoxes on the
UserForm to cells on a worksheet in my own Workbook, and on
pressing a "Prev" or "Next" CommandButton, I read the
next/previous row in the source WorkBook, then copy these
values to the Controlsource cells in my own Workbook. I have
verified this happens as designed.

The problem is that the UserForm's controls do not update with
the changed values in their Controlsources. The Repaint action
has no effect, apart from a blinking effect. Am I missing
something obvious, or misunderstood the intended behaviour of
contrls linked to Controlsources? The most common application
for UserForms does seem to be data input, but the Help topics
seem to suggest the Controlsource link operates in both
directions.


Cheers,
Francis K.

 

[Tom Ogilvy]

control source should work in both directions. I tested it in xl2000 and it
worked great for me. I tried both modal and non modal mode and with
screenupdating set to both false and true. The userform textboxes updated
without fault. Are you sure the control source is set to the right cells.
Did you use a sheet name such as

ControlSource: Sheet1!A1

 

[Francis Knight]

control source should work in both directions. I tested it in xl2000 and it
worked great for me. I tried both modal and non modal mode and with
screenupdating set to both false and true. The userform textboxes updated
without fault. Are you sure the control source is set to the right cells.
Did you use a sheet name such as

ControlSource: Sheet1!A1

Thanks for chipping in so quickly.

Yes, I used the sheet qualifier in the ControlSource property,
as displayed in the Properties pane with the UserForm selected
in the VBA editor.

The controls actually show the current source values when I do
this, which gave me confidence that I had them pointed to the
intended cells. At runtime, after the source cells are changed,
and the UserForm hasn't, if I go back and open it for editing,
then the controls update. It's almost as though the values seen
at design time get embedded in the UserForm.

Cheers,
Francis K.

 

[Tom Ogilvy]

Here is a possibility. Do you have calculation set to manual under
tools=>Options=>Calculation tab. When I tested this it did cause the
userform not to be updated.

 

[Francis Knight]

Here is a possibility. Do you have calculation set to manual under
tools=>Options=>Calculation tab. When I tested this it did cause the
userform not to be updated.

Had to wait until returning to work to check, but, "No" is
the answer. It's set to Automatic.

What it turns out I didn't have in the ControlSource was a
workbook qualifier to point it to the correct workbook. The
snag being that the workbook name will change from *.xls to
*.xla in due course. (I tested it without the filetype
extension, and it failed.)

Then my project threw up another problem


My 'superior' has provided the source data, which in one
column contains the number of a tool. Most of these have
identifiers like "37P 47401", which display fine in the
linked TextBox. Some though have an identifier like "337.000"
Instead of formatting as Text, he's formatted as Number with
three decimal places. I can do what it takes to get the same
appearance when I copy the value (and the .NumberFormat) to
my ControlSource cell, but the linked TextBox insists on
displaying it as an Integer. Have I missed a Property (I did
look) which determines this formatting of the TextBox?



Cheers,
Francis K.

 

[Tom Ogilvy]

No, the textbox doesn't necessarily pay attention to how the cell is
formatted.

You might consider breaking the control source link and managing the value
in the textbox with code. then you can use

Textbox1.Text = workbooks("Book1.xla").Worksheets(1).Range("B9").Text

and get the formatted version of the cell.

 

[Tom Ogilvy]

also, in case you are unaware, if you a sourcing from the same workbook as
the workbook with the userform, you can get the name of this workbook with
(strangely enough) thisworkbook.name. If you are accessing this object
and making your settings in some type of initialization code run on opening,
then this can avoid problems with changing the name (or location if that is
an issue, thisworkbook.fullname or path).