Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Text Box in Excel 2K3/2K7
I am wanting to create a floating text box in Excel 2003/2007 but am
currently stuck. The idea is that I will be able lock a text box with a few bullet points in the top right of the screen and that it will remain visible in that position regardless of where I scroll in the worksheet. Locking cells instead, or using Headers/Footers is not an option here. For this purpose, the text box has a name of "Box". I found this code online (along with several other examples) and replaced the name of the text box with my own, but could not get it (or any other example) to work. The following example gives the error "Method or Data member not found" and highlights ".Box"... Any help would be appreciated. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With ActiveWindow.VisibleRange Me.Box.Top = .Top Me.Box.Right = .Right End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Text Box in Excel 2K3/2K7
Place your TextBox on row 1 and freeze panes.
-- Regards Dave Hawley www.ozgrid.com "Raymond W." wrote in message ... I am wanting to create a floating text box in Excel 2003/2007 but am currently stuck. The idea is that I will be able lock a text box with a few bullet points in the top right of the screen and that it will remain visible in that position regardless of where I scroll in the worksheet. Locking cells instead, or using Headers/Footers is not an option here. For this purpose, the text box has a name of "Box". I found this code online (along with several other examples) and replaced the name of the text box with my own, but could not get it (or any other example) to work. The following example gives the error "Method or Data member not found" and highlights ".Box"... Any help would be appreciated. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With ActiveWindow.VisibleRange Me.Box.Top = .Top Me.Box.Right = .Right End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Text Box in Excel 2K3/2K7
It's likely that box was assigned to be the textbox elsewhere in the code. In your case you might do it within the same sub: VBA Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim box Set box = Shapes("box") Set xxx = ActiveWindow.VisibleRange With xxx.Resize(, xxx.Columns.Count - 1) box.Top = .Top box.Left = .Left + .Width - box.Width End With End Sub -------------------- Raymond W.;707981 Wrote: I am wanting to create a floating text box in Excel 2003/2007 but am currently stuck. The idea is that I will be able lock a text box with a few bullet points in the top right of the screen and that it will remain visible in that position regardless of where I scroll in the worksheet. Locking cells instead, or using Headers/Footers is not an option here. For this purpose, the text box has a name of "Box". I found this code online (along with several other examples) and replaced the name of the text box with my own, but could not get it (or any other example) to work. The following example gives the error "Method or Data member not found" and highlights ".Box"... Any help would be appreciated. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With ActiveWindow.VisibleRange Me.Box.Top = .Top Me.Box.Right = .Right End With End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198148 http://www.thecodecage.com/forumz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Text Box in Excel 2K3/2K7
Thanks for the responses, both of you. Freezing panes/cells is not an option
here for aesthetic reasons (not my spreadsheet). As for the name 'box', I had actually used another name originally that was far more original "MyAwesomeBox", but got tired of typing it in while trying various snippets of code from the web to make this work. That is when I changed it to 'box'. I will however try your suggestion when I get home in about 16 hours. The name of the box does not matter to me honestly. Thanks for your input, we'll see how it goes. "p45cal" wrote: It's likely that box was assigned to be the textbox elsewhere in the code. In your case you might do it within the same sub: VBA Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim box Set box = Shapes("box") Set xxx = ActiveWindow.VisibleRange With xxx.Resize(, xxx.Columns.Count - 1) box.Top = .Top box.Left = .Left + .Width - box.Width End With End Sub -------------------- Raymond W.;707981 Wrote: I am wanting to create a floating text box in Excel 2003/2007 but am currently stuck. The idea is that I will be able lock a text box with a few bullet points in the top right of the screen and that it will remain visible in that position regardless of where I scroll in the worksheet. Locking cells instead, or using Headers/Footers is not an option here. For this purpose, the text box has a name of "Box". I found this code online (along with several other examples) and replaced the name of the text box with my own, but could not get it (or any other example) to work. The following example gives the error "Method or Data member not found" and highlights ".Box"... Any help would be appreciated. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) With ActiveWindow.VisibleRange Me.Box.Top = .Top Me.Box.Right = .Right End With End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198148 http://www.thecodecage.com/forumz . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Floating Text Box in Excel 2K3/2K7
HA! I haven't had a chance to work on this until today. The code provided
works for the most part, the only feature lacking is to have it scroll when the user scrolls the work sheet, so it always remains visible, not just bouncing to the top on selection change. I don't know that this is possible and the box works as is. If anyone has a solution, thanks, if not - I am still happy. Great job mate! I changed the name of the box, and the code accordingly, so here is what I have: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim box Set box = Shapes("boxing") Set xxx = ActiveWindow.VisibleRange With xxx.Resize(, xxx.Columns.Count - 1) box.Top = .Top box.Left = .Left + .Width - box.Width End With End Sub "p45cal" wrote: It's likely that box was assigned to be the textbox elsewhere in the code. In your case you might do it within the same sub: VBA Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim box Set box = Shapes("box") Set xxx = ActiveWindow.VisibleRange With xxx.Resize(, xxx.Columns.Count - 1) box.Top = .Top box.Left = .Left + .Width - box.Width End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Floating Text Box help | Excel Worksheet Functions | |||
How do I create a floating text box with hyperlinks | Excel Worksheet Functions | |||
How do I set up a floating box in Excel? | Excel Worksheet Functions | |||
Floating toolbar and floating comment? | Excel Programming | |||
How do I add a floating text box in Excel? | Excel Discussion (Misc queries) |