| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Procedure too large
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
Does the .ControlSource property of the Textbox achieve this without code ?
NickHK "Ozgur Pars" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
Nick,
thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
Be more specific:
Me.TextBox1.ControlSource _ = Activeworkbook.Worksheets("sheet1").Range("a1").Ad dress(external:=True) Ozgur Pars wrote: 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
This works for me :
TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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 "Ozgur Pars" wrote in message ... 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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
Yes, but that's any empty string "", not a "[SPACE]".
NickHK "Ozgur Pars" ... 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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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 wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
Dave,
OK, I was using the wrong syntax for the WS/range, forgetting it should be a string. Thanks for the correction. 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 wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Procedure too large
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 "NickHK" wrote: Yes, but that's any empty string "", not a "[SPACE]". NickHK "Ozgur Pars" ... 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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: 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 "Ozgur Pars" wrote in message ... 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" wrote: This works for me : TextBox1.ControlSource = Range("A2").Address or TextBox1.ControlSource = "A2" It will refer to the active sheet. NickHK "Ozgur Pars" ... 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 "Ozgur Pars" wrote: Nick, thanks for the reply I will look into this property. Ozgur "NickHK" wrote: Does the .ControlSource property of the Textbox achieve this without code ? NickHK "Ozgur Pars" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA Compile error: Procedure too large? | Excel Discussion (Misc queries) | |||
| Compile Error: Procedure too large | Excel Programming | |||
| Procedure too large | Excel Programming | |||
| procedure too large | Excel Programming | |||
| Procedure Too Large problems | Excel Programming | |||
![Ozgur Pars[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
