Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Reading textbox contents using VBA

  1. #1
    Join Date
    29th April 2005
    Posts
    6

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd February 2005
    Location
    Stockholm, Sweden
    Posts
    24

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    29th April 2005
    Posts
    6

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    3rd February 2005
    Location
    Stockholm, Sweden
    Posts
    24

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    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


  6. #6
    Join Date
    29th April 2005
    Posts
    6

    Re: Reading textbox contents using VBA

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    29th April 2005
    Posts
    6

    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!!!!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Copy Contents Of Worksheet Textbox To Another
    By MikebAU in forum EXCEL HELP
    Replies: 3
    Last Post: August 14th, 2008, 10:08
  2. Saving textbox contents before a change
    By JDizzle in forum Excel and/or Access Help
    Replies: 3
    Last Post: April 21st, 2006, 22:29
  3. Reading contents of cell as file name
    By PBStone1 in forum EXCEL HELP
    Replies: 6
    Last Post: May 4th, 2005, 09:38
  4. Print contents of a MultiLine Textbox
    By Lydia Stone in forum EXCEL HELP
    Replies: 2
    Last Post: January 20th, 2005, 01:59
  5. Reading cell contents into variable
    By mikewaller in forum EXCEL HELP
    Replies: 4
    Last Post: October 26th, 2004, 21:16

Bookmarks

Posting Permissions

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