Announcement

Collapse
No announcement yet.

Reading textbox contents using VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reading textbox contents using VBA



    I have a textbox on an Excel worksheet.

    As part of a large series of macros, I want to read the contents of the textbox (completed by the user) and store it elsewhere in a normal cell

    I cannot work out the code to read the contents of the textbox



    HELP!

  • #2
    Re: Reading textbox contents using VBA

    Hi there,

    Try this one ! Just make sure to type the right name of the textbox in the code before you run it.
    Code:
    Sub ReadingTextbox()
        
        Range("H5").Value = ActiveSheet.TextBoxes("MyTextBox").Text
        
    End Sub
    Br,
    Pijay

    Comment


    • #3
      Re: Reading textbox contents using VBA

      Thanks

      The problem I now have is that the textbox is a commentary, which can often contain >>255 characters. This works on screen, but does not work in my code:

      Code:
      cells(1,1).value=activesheet.textboxes("commentary").text
      Any suggestions - I could do it by writing to files, but this way would be much easier (plus easier to understand by users)?? The cell contains 255 characters only (even though cells will accept much more and the source textbox contains much more)

      Help!

      Comment


      • #4
        Re: Reading textbox contents using VBA

        Hi again,

        I'm sorry, I don't have a solution for that. But if you could go through with transfering the comment to more than one cell you could do something like this?

        Code:
        Sub MyComment()
            
            If ActiveSheet.TextBoxes("Commentary").Characters.Count > 255 Then
                Cells(1, 1).Value = ActiveSheet.TextBoxes("Commentary").Characters(1, 255).Text
                Cells(2, 1).Value = ActiveSheet.TextBoxes("Commentary").Characters(256, 255).Text
                'You can just go on and on here...
                Cells(3, 1).Value = ActiveSheet.TextBoxes("Commentary").Characters(511, 255).Text
            Else
                Cells(1, 1).Value = ActiveSheet.TextBoxes("Commentary").Text
            End If
            
        End Sub
        I hope someone else can help you on this matter if this isn't it.

        Br,
        Pijay

        Comment


        • #5
          Re: Reading textbox contents using VBA

          Hi,


          Code:
          Sub MyComment()
               
              Dim lngPos As Long
              Dim rngOutput As Range
              
              Set rngOutput = Range("B1")
              rngOutput.Value = ""
              With ActiveSheet.TextBoxes("Commentary")
                  lngPos = 1
                  Do While lngPos <= .Characters.Count
                      rngOutput = rngOutput & .Characters(lngPos, 255).Text
                      lngPos = lngPos + 255
                  Loop
              End With
               
          End Sub

          Cheers
          Andy

          Comment


          • #6
            Re: Reading textbox contents using VBA

            Thanks for the help - am trying it now ...

            Comment


            • #7


              Re: Reading textbox contents using VBA

              I am now having a problem writing the information saved in a cells (>>255 characters) into a text box

              Code:
              Sub Load_Commentary(cr, cc, df, nf, rc)
                  '
              Dim tmpcomm(20)
                  '
                  Windows(nf).Activate
                  Sheets("Unit Routine Variables").Select
                  Set rngOutput = Range(Cells(cr, cc), Cells(cr, cc))
                  Windows(nf).Activate
                  '
                  lngPos = 1
                  lngTotal = Range(Cells(cr, cc), Cells(cr, cc)).Characters.Count
                  
                  inccomm = 0
                  Do While lngPos <= lngTotal
                      '
                      inccomm = inccomm + 1
                      Windows(nf).Activate
                      Sheets("Unit Routine Variables").Select
                      latestblock = Range(Cells(cr, cc), Cells(cr, cc)).Characters(lngPos, 255).Text
                      '
                      tmpcomm(inccomm) = latestblock
                      lngPos = lngPos + 255
                  Loop
                  '
                  Windows(df).Activate
                  Sheets("Area").Select
                  ActiveSheet.TextBoxes("commentary").Text = tmpcomm(1) & tmpcomm(2) & tmpcomm(3) & tmpcomm(4) & tmpcomm(5) & tmpcomm(6) & tmpcomm(7) & tmpcomm(8) & tmpcomm(9) & tmpcomm(10) & tmpcomm(11) & tmpcomm(12) & tmpcomm(13) & tmpcomm(14) & tmpcomm(15) & tmpcomm(16) & tmpcomm(17) & tmpcomm(18) & tmpcomm(19) & tmpcomm(20)
                  '
              End Sub
              doesnt appear to write anything to the text box (even though the array tmpcomm contains the text I need)

              HELP!!!!

              Comment

              Working...
              X