Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    As anyone who has seen posts related to Roland Willems' post (447963: "commentbox and freeze pane (1)") know, Comment Boxes don't work well in worksheets using Freeze Panes to keep rows and columns visible when scrolling through large sheets. I am looking for some alternatives to the "truncated" comment boxes, that do not involve the use of split screens and continue allowing us to use freeze panes. I also have concerns about slow loading of spreadsheets and slow processing of necessary calculations as the annual workbooks get larger. Here's the situation I face...

    We have several spreadsheets that are updated by a department which, by nature, has a relatively rapid turnover. In other words; there are a lot of new people having to enter data into the sheets without receiving much (or any) training. One of these worksheets (which I will use as the example here) is 20 columns wide, and has been growing by 3 to 4 rows per day for about 6 months (expected to increase to 50 records/day in the next 6 months). Freeze panes are used to keep the first column (record identifier) visible while scrolling right, and the first row (column headings) visible while scrolling down to enter new rows. Records (rows) must remain in ascending entry order (first at the top, last at the bottom).

    Since it was important for the users (entry operators) to be able to quickly (and simply) look up exact and thorough descriptions of what goes in each column without exiting the worksheet, we use comments in each column header cell to hold these descriptions (some being rather large by necessity). It also made it easy to teach various supervisors (some of which cannot even begin to grasp the concepts of 'Excel For Dummies') how to modify individual column comments when specific data for that field changed (sometimes on a monthly basis). The problem arrose when the worksheets grew beyond a single page, either vertically or horizontally. It became almost impossible to read all of the comments, especially when only 4 lines of a 30 line comment were visible.
    I increased the depth of the header row, which helped in some cases. However, since several of the last entered rows must remain visible to the operators as they enter new data, I cannot enlarge the header area above the freeze panes line enough to display all of the larger comments.

    Yes, I know, making data entry a "permanent" position and replacing certain managers would probably solve most of this issue, but it won't happen any time soon (some companies behave as if nepotism was a virtue). So, any ideas for solving this issue technically, from within Excel, would be greatly appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Instead of comment box, you could use "data validation" which does not have this problem. Allow "any value" from settings, and add the text in the "input message" (make sure you check "show when selected".

    You will have an additional potential "problem", since you are limited to 255 chars in the input box.

    Alternately if your use of the comment boxes is to add "help" you could use text boxes. They could be called from a "Help button" which determines the activecell column and puts the appropriate help message for that column. It could also be added to the right-click "cells" toolbar (the toolbar that pops up when you right-click a cell).

    Other options could be pulling up help when you dbl-click or right-click a cell (adding the macro to the appropriate event).

    More advanced options would be to create an "input form" and just keep them out of the spreadsheet. Let them add/edit/delete from a custom form where you have more control (and is more user-friendly) with validation testing, options for help, etc.

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Steve,

    Thank you. This gives me several alternatives that I had not thought of, or, was not aware of. Both the data validation and help options look very promising. I've heard of both, but have never utilized them. I'll need to learn how to use both to determine which will work best for our needs. Any suggestions for learning more, or will Excel help provide me with enough info to use them?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Using Data - Validation to add an input is not explicitly described, though the directions I give above should help.

    To create a button to run a macro is pretty "general topic" and there are not any specific instructions in the help to answer those questions. We could help here, if you would give us some details about what you would like.

    Steve

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Steve,

    Thank you. The Data Validation method was very simple to figure out from what you had already given me. Unfortunately, the 255 character limitation is definitely a problem for some of these help messages. So, I think that the best approach will be to use one of the text box methods you described. Though I still consider myself a novice with VBA, I'm capable enough to take this on with some examples and guidance (I'm especially good at taking someone else's code for a routine that's similar to my need, and modifying it for my purposes).
    What I have in mind is to add an "Entry Help" selection to the right-click menu for the cells. When the user chooses this selection, the routine determines which column the cell is in that was right-clicked (or is currently active) and displays a the appropriate help text in a box located in an out-of-the-way position (possibly below and to the right of the selected cell). I would like the user to be able to close the box with a button, or, have it automatically close when they exit the cell (Enter key, Tab key, or click in another cell). It would also be nice if the routine could grab the help text from the comments boxes which are attached to the row 1 cells (column headers) of each of the columns. That way I don't have to teach the supervisors a new way of updating the help information that changes periodically.
    As I mentioned previously, if I can find examples of routines that do comparable functions, I can usually make things work the way I want, especially with the magnificent help I've had from gurus like you, Hans, and the others in the Lounge (I've programmed in various legacy languages for nearly 30 years and I love to learn, otherwise, I would have given up on computers a long time ago). Point me in the right direction and I'll take it from there.

    Thanks,

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Do the users need to be able to see the message and input their data at the same time? I don't think they could have done this with the data validation option, and you seemed OK with that other than the character limitation, so maybe this would be an option for you...

    Could you insert a row above or below the headers, make it 1 pixel tall so it doesn't take away any screen space, and enter your help text in those cells? The row would be selectable and the help text would show up in the formula bar for them to read. Also, the people who are updating the comment/help text would not have any problems with making their changes.

    I realize it's not very exciting, but it should get the job done...

    - Brett

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    I think this is what you are after. It can be modified as needed. In a general module (in VB - Insert Module) add the following code:
    <pre>Option Explicit
    Public shpTB As Shape
    Public Const csName As String = "Data Entry Help"
    Sub RemoveHelp()
    On Error Resume Next
    Application.CommandBars("cell"). _
    Controls(csName).Delete
    End Sub
    Sub AddHelpToRtClick()
    Dim cbCtrl As CommandBarControl
    Dim sCmt As String
    On Error Resume Next
    RemoveHelp
    sCmt = ActiveCell.EntireColumn.Cells(1).Comment.Text
    On Error GoTo 0
    If sCmt <> "" Then
    With Application.CommandBars("cell"). _
    Controls.Add(Type:=msoControlButton, _
    before:=1, Temporary:=True)
    .Caption = csName
    .OnAction = "DisplayHelp"
    End With
    Application.CommandBars("cell"). _
    Controls(2).BeginGroup = True
    End If
    End Sub
    Sub DisplayHelp()
    Dim sCmt As String
    Dim sglTop As Single
    Dim sglLeft As Single
    Dim rAW As Range
    Set rAW = ActiveWindow.VisibleRange
    On Error Resume Next
    shpTB.Delete
    sCmt = ActiveCell.EntireColumn.Cells(1).Comment.Text
    On Error GoTo 0
    Application.ScreenUpdating = False
    If sCmt <> "" Then
    Set shpTB = ActiveSheet.Shapes.AddTextbox( _
    msoTextOrientationHorizontal, 1, 1, 1, 1)
    With shpTB.DrawingObject
    .AutoSize = True
    .Characters.Text = sCmt
    sglLeft = rAW.Left + rAW.Width / 2 - .Width / 2
    If sglLeft < 0 Then sglLeft = 0
    .Left = sglLeft
    sglTop = rAW.Top + rAW.Height / 2 - .Height / 2
    If sglTop < 0 Then sglTop = 0
    .Top = sglTop
    End With
    End If
    Application.ScreenUpdating = True
    Set rAW = Nothing
    End Sub</pre>


    The 2 public variables are for the textbox and the caption on the Menu. The shape is public so it can be used in multiple places once set. The caption text is also used more than once and this allows it be just changed in one place. Change the text here as desired.

    The first routine is "RemoveHelp" and it removes the item from the right-click menu. It will be removed (discussed later) when this sheet is not active, the workbook is closed, just to ensure that we don't add it multiple times, etc. the "on error" line is there in case the menu is not there when we try to remove it. The error will just be ignored

    The second routine, "AddHelpToRtClick" adds the help line to the right-click menu. It first makes sure that one does not exist by removing it, if it is there, then gets the comment from the first cell (row1) in the column of the active cell. If the comment is not a null (ie it has a comment) it adds a temporary item to the toolbar which will run the "DisplayHelp" macro in the first postion. It also adds the "spacer line" ("BeginGroup") immediately after it.

    The third routine, "DisplayHelp" is called when you select the line from the right click menu. The macro first deletes the shape (in case it is still there) then gets the comment from the first row in the column of the active cell. If the cell has a comment, it adds a text box, makes it "autosize", adds the comment from the first cell, then based on its size and the size and placement of the Window (via its visible range property) it center the textbox in the range.

    After adding the above code into a module, in the" project explorer", dbl-click the "sheet object" for your data sheet and add this code to the macro pane:
    <pre>Option Explicit
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    AddHelpToRtClick
    End Sub
    Private Sub Worksheet_Deactivate()
    RemoveHelp
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    shpTB.Delete
    End Sub</pre>


    This has 3 Macros which run at various events. When a "right-click" is pressed in a cell, it calls the "AddHelpToRtClick" macro. When the sheet is deactivated (another sheet in the workbook is selected) "RemoveHelp" is run, deleting the item from the right-click menu. When the selection is changed, the shape (textbox) is deleted

    Next, in the" project explorer", dbl-click the "thisworkbook object" and add this code to the macro pane:
    <pre>Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveHelp
    End Sub
    Private Sub Workbook_Deactivate()
    RemoveHelp
    End Sub</pre>


    Both these call the "RemoveHelp" (removing the item from the menu). The first runs when the workbook is closed, the second when the workbook is deactivated (another workbook is made active) in the excel session.

    Hope this helps,
    Steve

  8. #8
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Bret,

    Thanks for the suggestion. It would be preferable to have the help text visible while they're entering, which is another reason that the data validation method got a cool reception from my user base. If I can adapt Steve's code (<post#=520319>post 520319</post#>), I think everyone will be a lot happier.

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Steve,

    This is fantastic. Although I'm not sure that you left any work for me, I'll take the weekend to study and play with this code -- I should learn a thing or two from it. Thanks for all your work, and especially for your comments, it makes it very clear what I have to do and how I can work with it.

    Have a great weekend,

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Glad I could help.

    There are plenty of "enhancements" possible. For example, I added no error handling, which would be good to add...

    Steve

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Steve,

    I followed your instructions and added all the code, as you had provided, my test workbook. To try it out, I right clicked a cell (one in a column with a comment in row 1) and selected "Data Entry Help". I got the error Run time error '1004': Application-defined or object-defined error. I clicked the Debug button and found this statement, in the DisplayHelp() subroutine, highlighted: <pre> Set shpTB = ActiveSheet.Shapes.AddTextbox( _
    msoTextOrientationHorizontal, 1, 1, 1, 1)</pre>

    The arrow is pointing at the 'msoTextOrientationHorizontal' line (if that has any significance). What might be causing this?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    The line looks correct. Could you attach your test workbook so we can look at it to make sure you followed all directions (and also ensure I did not forget anything!)

    Steve

  13. #13
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    Steve,
    I'm including a zip file with the xls that I'm working with. Since my test data is real, I had to dummy up the data to insure no personal data would be revealed. I also had to shrink the size of the tables, even zipped they were too big to post. The sheets are protected to guard the formulas, but the password is blank (no password entered), so you can unprotect them if needed.

    Since both sheets have need of the help display routines, I added the macros to both sheet objects. I believe that I followed your instructions correctly. But it wouldn't be the first time, if I misunderstood something; and I'm sure it won't be the last time, either.

    Thanks for all the help,

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    The error comes because you are trying to add a shape to a protected worksheet.

    You must either add code to unprotect (and then re-protect) the workbook when you add or remove the object or add some code to workbook open and set the Userinterfaceonly argument of the protection method to true so that macros can work with protected workbooks.

    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comment Boxes with Freeze Panes Alternatives (Excel 2002 SP3)

    I saw another problem with the code. It seems that VB (or something) does not like to add text into a shape that is more than 255 chars (even though the shape can hold it). Here is a workaround. It adds it in 200-char pieces.

    <pre>Sub DisplayHelp()
    <font color=red> Dim i As Integer 'New
    Dim iLen As Integer 'New</font color=red>
    Dim sCmt As String
    Dim sglTop As Single
    Dim sglLeft As Single
    Dim rAW As Range
    Set rAW = ActiveWindow.VisibleRange
    On Error Resume Next
    shpTB.Delete
    sCmt = ActiveCell.EntireColumn.Cells(1).Comment.Text
    On Error GoTo 0
    Application.ScreenUpdating = False

    If sCmt <> "" Then
    <font color=red> iLen = 200 'New</font color=red>
    Set shpTB = ActiveSheet.Shapes.AddTextbox( _
    msoTextOrientationHorizontal, 1, 1, 1, 1)
    With shpTB.DrawingObject
    .AutoSize = True
    <font color=blue>'This does not work with long strings
    ' .Characters.Text = sCmt delete
    'Break into smaller sections</font color=blue>
    <font color=red> .Characters.Text = Left(sCmt, iLen) 'New
    For i = 1 + iLen To Len(sCmt) Step iLen 'New
    .Characters(i + iLen).Insert String:=Mid(sCmt, i, iLen) 'New
    Next 'New</font color=red>

    sglLeft = rAW.Left + rAW.Width / 2 - .Width / 2
    If sglLeft < 0 Then sglLeft = 0
    .Left = sglLeft
    sglTop = rAW.Top + rAW.Height / 2 - .Height / 2
    If sglTop < 0 Then sglTop = 0
    .Top = sglTop
    End With
    End If
    Application.ScreenUpdating = True
    Set rAW = Nothing
    End Sub</pre>


    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •