Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    2nd 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.
    VB:
    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:

    VB:
    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
    2nd 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?

    VB:
    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,314

    Re: Reading textbox contents using VBA

    Hi,


    VB:
    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

    VB:
    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 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

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