Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add another text box
on sheet1 i have commandbotton1
i want to click commandbutton1 and have the macro add a text box. if text box1's Top property = 20 i want text box2's Top property to be 40 and textbox3's to be 40 and so on. i am trying to work with a loop, but cant quite figure it out, Thanks...spence. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add another text box
Spence,
This is what I came up with: Private Sub CommandButton1_Click() Dim ctl As Control Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_count As Integer txtbox_top = 0 txtbox_left = 10 ' something to start with txtbox_height = 10 txtbox_width = 20 txtbox_count = 0 For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If ctl.Top txtbox_top Then With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count + 1) With ctl .Top = txtbox_top + 20 .Left = txtbox_left .Height = txtbox_height .Width = txtbox_width End With End Sub hth, Doug "spence" wrote in message ... on sheet1 i have commandbotton1 i want to click commandbutton1 and have the macro add a text box. if text box1's Top property = 20 i want text box2's Top property to be 40 and textbox3's to be 40 and so on. i am trying to work with a loop, but cant quite figure it out, Thanks...spence. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add another text box
it errored out on:
For Each ctl In Me.Controls i am actually trying to have it add a text box for every ite in range A1:A??. if there is something in A2 i want to add a second text box. thanks for your help...S -----Original Message----- Spence, This is what I came up with: Private Sub CommandButton1_Click() Dim ctl As Control Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_count As Integer txtbox_top = 0 txtbox_left = 10 ' something to start with txtbox_height = 10 txtbox_width = 20 txtbox_count = 0 For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If ctl.Top txtbox_top Then With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count + 1) With ctl .Top = txtbox_top + 20 .Left = txtbox_left .Height = txtbox_height .Width = txtbox_width End With End Sub hth, Doug "spence" wrote in message ... on sheet1 i have commandbotton1 i want to click commandbutton1 and have the macro add a text box. if text box1's Top property = 20 i want text box2's Top property to be 40 and textbox3's to be 40 and so on. i am trying to work with a loop, but cant quite figure it out, Thanks...spence. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
add another text box
Spence,
Sorry, stupid of me. I thought it was a userform, even though clearly not. The following is a start. I need more info on size and location of the textboxes. This puts the boxes in column B. Private Sub CommandButton1_Click() Dim ctl As Shape Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_count As Integer, i As Integer txtbox_top = 0 txtbox_left = ActiveSheet.Columns(1).Width ' something to start with txtbox_height = 10 txtbox_width = ActiveSheet.Columns(2).Width txtbox_count = 0 For Each ctl In ActiveSheet.Shapes If ctl.Type = 17 Then If ctl.Top txtbox_top Then MsgBox "t" With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl For i = 1 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - txtbox_count Set ctl = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, _ txtbox_left, txtbox_top, txtbox_width, txtbox_height) txtbox_top = txtbox_top + 20 Next i End Sub hopefully that's a little better, Doug "spence" wrote in message ... it errored out on: For Each ctl In Me.Controls i am actually trying to have it add a text box for every ite in range A1:A??. if there is something in A2 i want to add a second text box. thanks for your help...S -----Original Message----- Spence, This is what I came up with: Private Sub CommandButton1_Click() Dim ctl As Control Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_count As Integer txtbox_top = 0 txtbox_left = 10 ' something to start with txtbox_height = 10 txtbox_width = 20 txtbox_count = 0 For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If ctl.Top txtbox_top Then With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count + 1) With ctl .Top = txtbox_top + 20 .Left = txtbox_left .Height = txtbox_height .Width = txtbox_width End With End Sub hth, Doug "spence" wrote in message ... on sheet1 i have commandbotton1 i want to click commandbutton1 and have the macro add a text box. if text box1's Top property = 20 i want text box2's Top property to be 40 and textbox3's to be 40 and so on. i am trying to work with a loop, but cant quite figure it out, Thanks...spence. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
add another text box
Spence,
I left a msgbox line in there that you didn't need. Here it is again without it: Private Sub CommandButton1_Click() Dim ctl As Shape Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_count As Integer, i As Integer txtbox_top = 0 txtbox_left = ActiveSheet.Columns(1).Width txtbox_height = 10 txtbox_width = ActiveSheet.Columns(2).Width txtbox_count = 0 For Each ctl In ActiveSheet.Shapes If ctl.Type = 17 Then If ctl.Top txtbox_top Then With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl For i = 1 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - txtbox_count Set ctl = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, _ txtbox_left, txtbox_top, txtbox_width, txtbox_height) txtbox_top = txtbox_top + 20 Next i End Sub hth, Doug "spence" wrote in message ... it errored out on: For Each ctl In Me.Controls i am actually trying to have it add a text box for every ite in range A1:A??. if there is something in A2 i want to add a second text box. thanks for your help...S -----Original Message----- Spence, This is what I came up with: Private Sub CommandButton1_Click() Dim ctl As Control Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_count As Integer txtbox_top = 0 txtbox_left = 10 ' something to start with txtbox_height = 10 txtbox_width = 20 txtbox_count = 0 For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If ctl.Top txtbox_top Then With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count + 1) With ctl .Top = txtbox_top + 20 .Left = txtbox_left .Height = txtbox_height .Width = txtbox_width End With End Sub hth, Doug "spence" wrote in message ... on sheet1 i have commandbotton1 i want to click commandbutton1 and have the macro add a text box. if text box1's Top property = 20 i want text box2's Top property to be 40 and textbox3's to be 40 and so on. i am trying to work with a loop, but cant quite figure it out, Thanks...spence. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
add another text box
Spence,
Hoo boy. So, this is how I'm teaching myself programming, ya see... Anyways, I noticed that the last iteration didn't quite work if there were already textboxes - it wrote the first new one over the last old one. So, after this I'm going to wait and see if this is even on the right track for you.: Private Sub CommandButton1_Click() Dim ctl As Shape Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_spacing As Integer, txtbox_count As Integer, i As Integer txtbox_top = 0 txtbox_left = ActiveSheet.Columns(1).Width txtbox_height = 10 txtbox_width = ActiveSheet.Columns(2).Width txtbox_spacing = 20 txtbox_count = 0 For Each ctl In ActiveSheet.Shapes If ctl.Type = 17 Then If ctl.Top txtbox_top Then With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl If txtbox_count 0 Then txtbox_top = txtbox_top + txtbox_spacing End If For i = 1 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - txtbox_count Set ctl = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, _ txtbox_left, txtbox_top, txtbox_width, txtbox_height) txtbox_top = txtbox_top + txtbox_spacing Next i End Sub hth, Doug "spence" wrote in message ... it errored out on: For Each ctl In Me.Controls i am actually trying to have it add a text box for every ite in range A1:A??. if there is something in A2 i want to add a second text box. thanks for your help...S -----Original Message----- Spence, This is what I came up with: Private Sub CommandButton1_Click() Dim ctl As Control Dim txtbox_top As Integer, txtbox_left As Integer, _ txtbox_height As Integer, txtbox_width As Integer, _ txtbox_count As Integer txtbox_top = 0 txtbox_left = 10 ' something to start with txtbox_height = 10 txtbox_width = 20 txtbox_count = 0 For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then If ctl.Top txtbox_top Then With ctl txtbox_top = .Top txtbox_left = .Left txtbox_height = .Height txtbox_width = .Width End With End If txtbox_count = txtbox_count + 1 End If Next ctl Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count + 1) With ctl .Top = txtbox_top + 20 .Left = txtbox_left .Height = txtbox_height .Width = txtbox_width End With End Sub hth, Doug "spence" wrote in message ... on sheet1 i have commandbotton1 i want to click commandbutton1 and have the macro add a text box. if text box1's Top property = 20 i want text box2's Top property to be 40 and textbox3's to be 40 and so on. i am trying to work with a loop, but cant quite figure it out, Thanks...spence. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using TEXT and &TEXT - display numbers with commas, underline text | Excel Discussion (Misc queries) | |||
Default font for pasted text in text boxes - Excel 2007 | Excel Discussion (Misc queries) | |||
Text does not display in "Text boxs" and when wrapping text in a c | Excel Discussion (Misc queries) | |||
text (3750 char.)truncates with text wrap and row heigh adjusted? | Excel Discussion (Misc queries) | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |