Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Thumbs up Excel code requiring input in textbox

    Hi

    I have a workbook with several sheets. The sheet named "Recap" has 6 textboxes where I want the user to enter text. If the user does not fill-in the textboxes I want the code to activate a message. I have the following code but it is not working!

    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Trim(TextBox1.Text) = "" Then
    MsgBox "You must enter data"
    Cancel = True
    End If
    End Sub

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    There is no event Textbox_Exit. Try

    Code:
    Private Sub TextBox1_LostFocus()
    If TextBox1.Value = "" Then
    MsgBox "You must enter data"
    End If
    End Sub
    Place in the worksheet module

  3. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    There is only a Textbox_Exit if a textbox is placed on a form

    HTH,
    Maud

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Jean,

    Did you add the "textboxes" via the Insert tab or Drawing toolbar? If so they are not really Textboxes but rather Shapes. Unfortunately Shapes do not have events like Exit AFAICT. You can assign a macro to the shape that will execute when clicked like this:
    Code:
    Option Explicit
    
    Sub Test()
    
       Dim shp As Shape
       
       Set shp = ActiveSheet.Shapes(Application.Caller)
       
       If Trim(shp.TextFrame.Characters.Text) = "" Then
            MsgBox "Please Add Text", vbOKOnly + vbCritical, _
                   shp.Name
       End If
       
    End Sub
    Unfortunately, this executes when you enter the shape not when you exit!

    The other option would be some checking code that would be executed looping through the shapes on the sheet.
    Code:
    Sub CkhShapes()
    
       Dim shp As Shape
       
       For Each shp In ActiveSheet.Shapes
          
          If shp.Type = 17 Then   '*** Textbox ***
            If Trim(shp.TextFrame.Characters.Text) = "" Then
               MsgBox "Please Add Text", vbOKOnly + vbCritical, _
                         shp.Name
            End If
          End If
          
       Next shp
    
    End Sub
    Test workbook: VBA - Excel - Working With Shapes.xlsm
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    JeanM,

    Insert Tab that RG speaks of is not to be confused with the Insert button on the Developer tab which does allow the adding of active X controls which contain event subroutines.

    activeX.png

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Jean,

    I got to thinking and here's a better version. The following code is placed in the This Workbook module.
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
       Dim shp As Shape
      
       For Each shp In ActiveSheet.Shapes
          
          If shp.Type = 17 Then   '*** Textbox ***
            If Trim(shp.TextFrame.Characters.Text) = "" Then
               MsgBox "Please Add Text", vbOKOnly + vbCritical, _
                         shp.Name
               Cancel = True
            End If
          End If
          
       Next shp
       
    '*** If no missing text then Save in not already saved ***
    
       If Not Cancel Then
         If Me.Saved = False Then Me.Save
       End If
       
    End Sub
    When the user attempts to close the workbook the user will be prompted for any missing text items and not let the workbook be closed until all are filled in. Upon exiting it will save the workbook if it hasn't been saved already.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JeanM (2014-09-01)

  8. #7
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi

    I did use a textbox with the drawing tool versus embedded forms textbox. The code worked great but I would like it to work on any worksheet tab in the workbook instead of the active sheet. To elaborate it works if I am on the "Recap" sheet but I have several sheets in the workbook and my user group might not even click on the recap sheet. How do I write the code so it is not the active sheet?
    Jean

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    Jean,

    This will check all sheets:
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
       Dim shp As Shape
       Dim sht As Worksheet
      
       For Each sht In ActiveWorkbook.Sheets
       
          For Each shp In sht.Shapes
          
             If shp.Type = 17 Then   '*** Textbox ***
               If Trim(shp.TextFrame.Characters.Text) = "" Then
                  MsgBox "Please Add Text", vbOKOnly + vbCritical, _
                         shp.Name & " on sheet: " & sht.Name
                  Cancel = True
               End If
             End If
          
          Next shp
       
       Next sht
       
    '*** If no missing text then Save in not already saved ***
    
       If Not Cancel Then
         If Me.Saved = False Then Me.Save
       End If
       
    End Sub
    missingtxtmsg.JPG
    Again place code in the Workbook Module. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #9
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi

    That works great. Thank you for the help.

    Jean

  11. #10
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    RG,

    Kudos on your intuitive insight that anyone would want to use a drawing tool Textbox as an input mechanism on a sheet. Never would have guessed that especially since the OP's macro is exactly what the code would be for an active X control on exit. Besides, every time you click on a drawing tool textbox to input, you get an edit frame.

Posting Permissions

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