Procedure too large

[Guest]

Hi,
I am currently working on a userform where I use textboxes to display from a
workbook. Everytime the workbook calculates the texboxes are updated. It was
going all fine until I hit a limit and the above compile error message came
up.

If anyone could help me make my code simpler and more efficient I would
greatly appreciate it.
I use these code(of course hundreds of them because I have several option
buttons and the value of the txtboxes must change) when the workbook is
calculated:
UserForm1.TextBox1.Value = Sheets("Calculation").Range("A1").Value
UserForm1.TextBox1.Text = Sheets("Calculation").Range("A1").Text

I was wondering if one has to specify the text also everytime the value is
changing? If I can fix the text style I can probably get rid of the problem.

Thanks for your help,
Ozgur

 

[NickHK]

Does the .ControlSource property of the Textbox achieve this without code ?

NickHK

 

[Guest]

Nick,
thanks for the reply I will look into this property.

Ozgur

 

[Guest]

Sorry for the neverending questions but I am missingsomething here and I
could not get it from the help or the net.

I am trying to set the controlsource property for a textbox1 on a userform1.
I replicated the example from the help file:

Private Sub UserForm1_Initialize()
TextBox1.ControlSource = "a1"
End Sub

That seems not to work. I tried to input the link in the properties menu
manually but I am getting a "Invalid property value" error message.

What am I missing or doing wrong here?

Thanks,
Ozgur

 

[Dave Peterson]

Be more specific:

Me.TextBox1.ControlSource _
= Activeworkbook.Worksheets("sheet1").Range("a1").Address(external:=True)

 

[NickHK]

This works for me :
TextBox1.ControlSource = Range("A2").Address
or
TextBox1.ControlSource = "A2"

It will refer to the active sheet.

NickHK

 

[Guest]

Dave,Nick,
thanks for your replies. I tried both of your posts with mixed results. When
I put Dave's code in the initialize event of the userform it worked. However
when I tried it later again it still gave me a "Invalid Property Value"...I
can't figure out why it would not work the second time around (didn't work
anymore at all)...
Am I totally misunderstanding this feature? Is is controlsource not to link
the control with a excel sheet cell. Howcome invalid property message when?
(I am using Excel 2003 for what is worth)

Anyway appreciate your help,
Ozgur

 

[NickHK]

From what I can tell, it only works with Activesheet, although there is no
mention of this limitation in Help.
Any worksheet reference is ignored and the relevant address on the
ActiveSheet used.

NickHK

 

[Guest]

Sorry to bother you with maybe trivial stuff but here is the help expanation:

"ListBox Control, BoundColumn, ControlSource, RowSource Properties Example

The following example uses a range of worksheet cells in a ListBox and, when
the user selects a row from the list, displays the row index in another
worksheet cell. This code sample uses the RowSource, BoundColumn, and
ControlSource properties.

To use this example, copy this sample code to the Declarations portion of a
form. Make sure that the form contains a ListBox named ListBox1. In the
worksheet, enter data in cells A1:E4. You also need to make sure cell A6
contains no data.

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 5
ListBox1.RowSource = "a1:e4"
ListBox1.ControlSource = "a6"
'Place the ListIndex into cell a6
ListBox1.BoundColumn = 0
End Sub
"

I did copy this into a userform and if you exclude the controlsource part of
the code it works but if you try to use it as above stated it still gives an
"Invalid Property
Value" message. I thought may be its because I had data in the A6 cell but
I tried it with a empty cell and still some old same old.
Could the help file be wrong??? Not being abale to figure it out is killing
me...

Ozgur

 

[NickHK]

The example works for me as written.

Well, spoke (wrote) a bit too soon.
Worked fine, then stopped with same error as you have received.

OK, you need to pay attention to the line of help that says "You also need
to make sure cell A6 contains no data.".
Add a line
Range("A6").Value="" 'Or whatever range you use for .ControlSource
Before you set the .ControlSource
ListBox1.ControlSource = "A6"

NickHK

 

[Guest]

Nick,
I don't want to take up too much of your time but did you actually set the
cell to " " and it worked?
I did what you proposed but I still get the same error. I did not work.

Thanks,Ozgur

 

[NickHK]

Yes, but that's any empty string "", not a "[SPACE]".

NickHK

 

[Dave Peterson]

You can specify the string so that it points at a different sheet:

'sheet with linked cell'!a5

Much like a formula in a cell.

 

[NickHK]

Dave,
OK, I was using the wrong syntax for the WS/range, forgetting it should be a
string.
Thanks for the correction.

NickHK

 

[Guest]

Nick I never did leave a space between the "" signs. I did write it this way
because it seemed to be less confusing...wrong choice)
Anyway thanks again,
Ozgur

Yes, but that's any empty string "", not a "[SPACE]".

NickHK

Nick,
I don't want to take up too much of your time but did you actually set the
cell to " " and it worked?
I did what you proposed but I still get the same error. I did not work.

Thanks,Ozgur