Creating a dynamic text box to record notes

Here are three macros that can create a textbox and/or position it on the screen, clear its contents and hide it. Put them in a regular module sheet and they can be used on any worksheet.

Sub ShowTextBox()
Dim shp As Shape
Dim ws As Worksheet
Dim wn As Window
Dim sngHeight As Single, sngLeft As Single, sngTop As Single, sngWidth As Single

Set ws = ActiveSheet
On Error Resume Next
Set shp = ws.Shapes("Notes")
On Error GoTo 0
Set wn = Application.ActiveWindow
sngLeft = wn.Left + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Left + wn.Width * 0.65
sngWidth = 200
sngTop = wn.Top + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Top + wn.Height / 8
sngHeight = 300

If shp Is Nothing Then
    Set shp = ws.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, Left:=sngLeft, Top:=sngTop, Width:=sngWidth, Height:=sngHeight)
    shp.Name = "Notes"
Else
    shp.Top = sngTop
    shp.Left = sngLeft
    shp.Visible = msoTrue
End If

shp.Select
Selection.PrintObject = msoFalse

End Sub

Sub HideTextBox()
On Error Resume Next
ActiveSheet.Shapes("Notes").Visible = msoFalse
On Error GoTo 0
End Sub

Sub ClearTextBox()
On Error Resume Next
ActiveSheet.Shapes("Notes").TextFrame.Characters.Text = ""
'ActiveSheet.Shapes("Notes").Visible = msoFalse
On Error GoTo 0
End Sub

Select allOpen in new window

I have previously achieved something similar with a UserForm (automatically shown upon selection of a cell, or via a user-based action, such as a right-click) that includes a textbox that can be moved & resized then, upon confirming the data entered, returns that to the original cell.

If Brad's suggestion above does not meet your requirements, I can offer a working example of what I have implemented in previous projects.

Brad and Fanpages,

I put all three macros in Module1 and tried to run it in WS1 and then WS2.  I got the same error on this line:   Set shp = ws.Shapes("Notes")

Thanks,

B.

The code as posted is working for me in Excel 2013. Which version are you using?

I put the code in Module1, and ran it on two different worksheets without incident. See sample workbook.

If my workbook works at your end, but yours does not--please post your workbook.
TextboxForNotesQ28736558.xlsm

I initially thought to suggest a modeless userform that carried a textbox. I found myself unable to satisfy the formatting requirements, however (make text bold or change font color). Perhaps fanpages has a workaround for that.

Brad,

Downloaded your example and get the same error.  I'm using Excel 2010.  

B.
D--Data-Data-Temp-Macro-for-Notes.xlsm

I'm not seeing any error with either my workbook or yours in my 32-bit Excel 2010.

Please try rebooting your computer and repeating the test at your end. I am guessing there may be an issue with your installation of Excel.

If the problem continues, please post the error message you receive and which line and which macro it occurs on.

Also, I suggest that you check the box for "Require variable declaration" in the Tools...Options...General dialog in the VBA Editor. This will put "Option Explicit" at the top of any newly created module. This is good practice and will help you find typos when variables are misspelled.

Brad,

Great comments!  OK.... added "Req. Var. Dec." to my profile.  I'm running Excel 2010 32-bit.  I'll pass the WB over to my laptop and see if it will run.  Also will reboot my system.

Back shortly.

B.

Brad,

You were right!  My laptop version of the code works.  Only one change needed.  Can you make the text box expand to meet any excess text?  In other words, can it dynamically expand or contract to account for more or less text?

Thank you,

B.

I added a single statement to the ShowTextBox sub to AutoSize the textbox. I also added a sub to do it. You will need to decide whether you want the textbox to start real small in size and grow as needed (the single statement)--or to start with a standard size and choose when you want to resize it (the sub).

Sub ShowTextBox()
Dim shp As Shape
Dim ws As Worksheet
Dim wn As Window
Dim sngHeight As Single, sngLeft As Single, sngTop As Single, sngWidth As Single

Set ws = ActiveSheet
On Error Resume Next
Set shp = ws.Shapes("Notes")
On Error GoTo 0
Set wn = Application.ActiveWindow
sngLeft = wn.Left + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Left + wn.Width * 0.65
sngWidth = 200
sngTop = wn.Top + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Top + wn.Height / 8
sngHeight = 300

If shp Is Nothing Then
    Set shp = ws.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, Left:=sngLeft, Top:=sngTop, Width:=sngWidth, Height:=sngHeight)
    shp.Name = "Notes"
Else
    shp.Top = sngTop
    shp.Left = sngLeft
    shp.Visible = msoTrue
End If

shp.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
shp.Select
Selection.PrintObject = msoFalse

End Sub

Sub ResizeTextbox()
On Error Resume Next
ActiveSheet.Shapes("Notes").TextFrame2.AutoSize = msoAutoSizeShapeToFitText
On Error GoTo 0
End Sub

Sub HideTextBox()
On Error Resume Next
ActiveSheet.Shapes("Notes").Visible = msoFalse
On Error GoTo 0
End Sub

Sub ClearTextBox()
On Error Resume Next
ActiveSheet.Shapes("Notes").TextFrame.Characters.Text = ""
'ActiveSheet.Shapes("Notes").Visible = msoFalse
On Error GoTo 0
End Sub

Select allOpen in new window

TextboxForNotesQ28736558.xlsm

One more question Brad,  can you add a line that fires the macro when you select Alt. N or some keyboard action?

B.

It's very easy to set the shortcut keys manually:
1. ALT + F8 to open the macro selector
2. Select your macro
3. Click the Options button
4. Choose your shortcut key combination. I suggest using CTRL + Shift + some character to avoid displacing an existing Excel shortcut.

Here is a macro that sets the shortcut keys:

Sub SetShortcutKeys()
Application.MacroOptions Macro:="ShowTextBox", Description:="", ShortcutKey:="S"
Application.MacroOptions Macro:="ResizeTextBox", Description:="", ShortcutKey:="R"
Application.MacroOptions Macro:="ClearTextBox", Description:="", ShortcutKey:="C"
Application.MacroOptions Macro:="HideTextBox", Description:="", ShortcutKey:="H"
End Sub

Select allOpen in new window

Brad,

Your code works in your WB.  In my WB, I put the code in a Module.  But I'm getting a "Compile error; member or data member not found".  

The error is occurring at this point at the end of the Show Notes Sub:  

Selection.PrintObject = msoFalse

Do you care whether the textbox prints out? I put that statement in so it does not. But if you don't care, just delete that statement.

Alternatively, try using 0 instead of msoFalse. This change will work if (for some unknown reason) your Excel 2010 install doesn't have the named constant msoFalse.

OK.... I did this substitution:

Selection.PrintObject = 0

'Selection.PrintObject = msoFalse

But still get the error.  I've got both of your code modules in my production version and have connected a button to the sub.

B.

Could you post the workbook with the problem?

Brad,  The major production WB I can't upload to EE.  

I just tried to trouble shoot this again and got the following results.  I opened my WB and selected to run the macro that you had in your WB from my WS.  It ran fine within my WB/WS.  I then tried my in WB macro and continued to get a "compile error; method or data not found".  at this particular point:

.PrintObject =

It's the same macro.  

Any ideas?

B.

Do you need to prevent the textbox from printing? If not, just comment that statement out.

With the code in there, When will it "print"?  I was hoping to, at a next step, add it to the print routine I have embedded when the Print button was later selected. Can the text output be simply dumped to another WS and then printed from there later?

I'll test it now with commenting it out.

B.

OK...commented it out and it works!  How do I control the location and size of the Text Box when it is displayed?

B.

I added the Selection.PrintObject statement to the code to prevent the textbox from printing. When I add a textbox to a worksheet to use as a scratchpad in my day job, I don't want it to be part of the printout. But if your notes will be valuable to people looking at the printout of your worksheet, you would want to comment that statement out (as you were forced to do by the recurring error message). By default, textboxes are printed with the worksheet.

Size and location are controlled by these statements in sub ShowTextBox:

sngLeft = wn.Left + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Left + wn.Width * 0.65
sngWidth = 200
sngTop = wn.Top + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Top + wn.Height / 8
sngHeight = 300

Select allOpen in new window

Brad,  got it!  One last question;  When I change the numbers/variables above and then fire the macro to display the Text box, it is still the same size displayed..... I must be missing something simple.

B.

Brad.... I love this macro!!!   One final question; if I want to clear the Notes you right now have an with Activesheet statement.  But if the clear the entire WB macro runs from another WS, it doesn't clear the Notes.  Is there a single line that tells the macro to clear the Note(s) regardless of weather or not you are on the active page?  Like a global command to clear?

B.

Remember when you asked for the textbox to be automatically resized according to the amount of text? I gave you two means of doing it: a sub ResizeTextBox and a single statement in the ShowTextBox sub:

shp.TextFrame2.AutoSize = msoAutoSizeShapeToFitText

Select allOpen in new window

Both approaches cause you to lose control over the size of the textbox (via sngHeight and sngWidth in sub ShowTextBox). So you may want to comment out the AutoSize statement in ShowTextBox and use the ResizeTextBox sub only when you need it.

I wrote the subs with the idea in mind that they would always work on the active worksheet. If you want to clear all the text from the various Notes textboxes (no matter which worksheets they are on), you can use a sub like the following:

Sub ClearAllNotesTextBoxes()
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    ws.Shapes("Notes").TextFrame.Characters.Text = ""
    'ws.Shapes("Notes").Visible = msoFalse
Next
On Error GoTo 0
End Sub

Select allOpen in new window

Note that the above sub clears the text, but does not delete the textbox. Also note that I commented out a statement that makes the textbox hidden. It's up to you whether to use that statement or not.

Brad,

When I open the Text Box, and I put text in and then run the macro directly, it does just as you say.  It eliminates the text.  HOWEVER, when I put it into my Clear All Macro as a sub, it doesn't clear the note if I reopen it.  I think it may be either the order of all these subs are doing something to prevent the note pad from clearing. Here is my clear all sub.

Sub Clearall()
        Application.EnableEvents = False
        Application.ScreenUpdating = False
       ActiveSheet.Unprotect Password:="jam"
        ClearUseCaseSelections
        ClearFinancialInput
        ClearDetailedFlows
        ClearCashflowAnalysis
        ClearSplash
        ClearAllNotesTextBoxes
        HideTextBox
        Hidealltabs
        Expand_ValueProposition
        ResetPriorities
        ResetAssetFailure
        ClearCheckboxes
        Contract_Model
        clearSourcesUsesFields
    Application.ScreenUpdating = True
    Application.EnableEvents = True
        ActiveSheet.Protect Password:="jam"
End Sub

Any ideas?

B.

The macro won't clear a text box if the worksheet is protected and the Edit Objects box wasn't checked when protection was established.

Another possibility is to re-establish protection with the UserInterfaceOnly property. Doing so allows macros to make many (but not all) changes on a protected worksheet. The best time to do this is when you are opening the workbook, but it could be integrated into my sub that clears the Notes as well.

Dim ws As Worksheet
ws.Protect Password:="jam", UserInterfaceOnly:= True

Select allOpen in new window

A+++!  I love this macro.  Thank you so much for sharing and also for helping me integrate it into my production WB.  

Always love to work with you  (and unfortunately, you have to do most of the work!).  You are a great professional.

B.

Brad,

One thing.  Is there a way to make the Text Box that appears, UNLOCKED, while the rest of the spreadsheet is Locked?  Right now if I fire the macro, it unlocks the entire spreadsheet.  I have it relocking when hiding...but it would be ideal if when you call the TExt box, it keeps the rest of the sheet locked so as not to let people disturb the structure.

Sorry for the scope creep here.  And if it is complicated, don't worry about it.

B.

Manual method:
When you protect the worksheet, make sure the box is checked to allow the user to Edit objects

VBA method:
When you apply password protection, make sure you set the DrawingObjects parameter to False

ws.Protect Password:="jam", UserInterfaceOnly:=True, DrawingObjects:=False

Select allOpen in new window

OK... just tried it but had a weird thing happen.  When I add the line of VB code and comment out my unprotect line, it unprotects the entire spreadsheet when the notes macro is fired, just as if I had used the unprotect command...but the sheet still indicates that it is protected (asks for password to unprotect).  Could this be that my Userinterfaceonly has some profile that unlocks the entire sheet when set to "true"? I'm not sure how to view or see my userinferfaceonly profile.  

Here are the two lines I'm using:

ws.protect password:="jam", userinterfaceonly:=true, drawingobjects:=false
'Activesheet.unprotect password:="jam"

B.

UserInterfaceOnly:= True means that macros can do things that users are prohibited from doing. The worksheet protection applies only to things the user tries to do from the worksheet user interface. It doesn't work for everything, but a lot of the time you can avoid the need to unprotect the worksheet while a macro is running.

The statement I suggested does not unprotect the worksheet. Instead, it applies protection where there was none and reapplies the protection if it was already in force. In this latter case, you will want to make sure that the protection parameters include those you had been using in the worksheet.protect statement you had been using at the end of your macros.

The best time to apply the UserInterfaceOnly:= True protection is when you open the workbook. You might have a sub that applies worksheet protection to all worksheets, and use the UserInterfaceOnly:= True parameter. Call that sub from the Workbook_Open event sub.

I suggest commenting out the worksheet unprotect/protect statements in all your other subs. Then test whether all your macros work without runtime error. For those few macros that still encounter a runtime error due to worksheet protection, you will still need to unprotect the worksheet and reapply it at the end of the macro.

So I have the code in the WS; if it needs to launch at start up, should it be in a module instead?  It doesn't unprotect the WS when I fire the macro for the Notes..... but it does let me access the cells whereas before they were protected and I couldn't access them.  

I think it may have more to do with where in the code I have put the line.  Here's the entire Macro.

Option Explicit
Sub ShowTextBox()
Dim shp As Shape
Dim ws As Worksheet
Dim wn As Window
Dim sngHeight As Single, sngLeft As Single, sngTop As Single, sngWidth As Single

Set ws = ActiveSheet
On Error Resume Next
Set shp = ws.Shapes("Notes")
On Error GoTo 0
Set wn = Application.ActiveWindow
sngLeft = wn.Left + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Left + wn.Width * 0.5
sngWidth = 10
sngTop = wn.Top + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Top + wn.Height / 8
sngHeight = 800

ws.Protect Password:="jam", userinterfaceonly:=True, DrawingObjects:=False
'ActiveSheet.Unprotect Password:="jam"

If shp Is Nothing Then
    Set shp = ws.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, Left:=sngLeft, Top:=sngTop, Width:=sngWidth, Height:=sngHeight)
    shp.Name = "Notes"
Else
    shp.Top = sngTop
    shp.Left = sngLeft
    shp.Visible = msoTrue
End If

shp.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
shp.Select
'Selection.PrintObject = 0

'Selection.PrintObject = msoFalse

End Sub

I changed the code in Module1 as shown below. I added a sub called UserInterfaceOnly that applies protection to each worksheet using password "jam", UserInterfaceOnly:= True and DrawingObjects:= False. I also deleted the corresponding statement from subs ClearAllNotesTextBoxes and ShowTextBox.

'This code goes in Module1
Sub ShowTextBox()
Dim shp As Shape
Dim ws As Worksheet
Dim wn As Window
Dim sngHeight As Single, sngLeft As Single, sngTop As Single, sngWidth As Single

Set ws = ActiveSheet
On Error Resume Next
Set shp = ws.Shapes("Notes")
On Error GoTo 0
Set wn = Application.ActiveWindow
sngLeft = wn.Left + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Left + wn.Width * 0.65
sngWidth = 200
sngTop = wn.Top + ws.Cells(wn.ScrollRow, wn.ScrollColumn).Top + wn.Height / 8
sngHeight = 300

If shp Is Nothing Then
    Set shp = ws.Shapes.AddTextbox( _
        Orientation:=msoTextOrientationHorizontal, Left:=sngLeft, Top:=sngTop, Width:=sngWidth, Height:=sngHeight)
    shp.Name = "Notes"
Else
    shp.Top = sngTop
    shp.Left = sngLeft
    shp.Visible = msoTrue
End If

'shp.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
shp.Select
Selection.PrintObject = msoFalse

End Sub

Sub ResizeTextBox()
On Error Resume Next
ActiveSheet.Shapes("Notes").TextFrame2.AutoSize = msoAutoSizeShapeToFitText
On Error GoTo 0
End Sub

Sub HideTextBox()
On Error Resume Next
ActiveSheet.Shapes("Notes").Visible = msoFalse
On Error GoTo 0
End Sub

Sub ClearAllNotesTextBoxes()
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
    ws.Shapes("Notes").TextFrame.Characters.Text = ""
    'ws.Shapes("Notes").Visible = msoFalse      'Hide the Notes textboxes
Next
On Error GoTo 0
End Sub

Sub ClearTextBox()
On Error Resume Next
ActiveSheet.Shapes("Notes").TextFrame.Characters.Text = ""
'ActiveSheet.Shapes("Notes").Visible = msoFalse
On Error GoTo 0
End Sub

Sub SetShortcutKeys()
Application.MacroOptions Macro:="ShowTextBox", Description:="", ShortcutKey:=""
Application.MacroOptions Macro:="ResizeTextBox", Description:="", ShortcutKey:=""
Application.MacroOptions Macro:="ClearTextBox", Description:="", ShortcutKey:=""
Application.MacroOptions Macro:="HideTextBox", Description:="", ShortcutKey:=""
End Sub

Sub UserInterfaceOnly()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.ProtectContents Or ws.ProtectDrawingObjects Then
        ws.Protect Password:="jam", UserInterfaceOnly:=True, DrawingObjects:=False
    End If
Next
End Sub

Select allOpen in new window

I added code in the ThisWorkbook code module to call sub UserInterfaceOnly.

'This code goes in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
UserInterfaceOnly
End Sub

Select allOpen in new window

Jim,
I feel like we are not quite connecting on the details in this thread. If you would like me to look at the actual workbook, you know how to reach me.

Brad