Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Other Office > Word VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Word VBA Discuss using VBA to program Word.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Word VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 28th, 2011, 11:11 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation Loop to insert excel data into word userform

Hi,

I am really struggling on creating a loop that takes data from a column starting at cell (2,3).

I want to insert that data into a series of textboxes named:
txtExclusion1
txtExclusion2
txtExclusion3
...

I would prefer to use a loop that stops when it gets to the last cell that has data in it. I do not think I can really use a counter.

I am also fairly new to VBA so please could I get comments of what is happening in the code.

Thanks
Reply With Quote
  #2 (permalink)  
Old June 28th, 2011, 01:20 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

There are many ways to find the last cell

1) Use SpecialCells

cells.specialcells(xlcelltypelastcell).Row

2) Use End and then move up
Range("C65536").End(xlup).Select

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #3 (permalink)  
Old June 29th, 2011, 04:32 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation whole loop is the problem

Hi,

Thanks for getting back to me so quickly. However, I have a problem with pretty much every aspect of the loop not just the special cells (although I do still need to see how the special cells work in context). I am also not sure how the computer knows which workbook it comes from (in this case oXB).

Below is the sort of thing I am trying to do but I do not know enough about loops to be able to do this by myself. the [] include numbers that go up by +1 every time it loops. Also should the lastRow be a range?

Code:
Set lastRow = oXB.sheet(1).Cells.SpecialCells(xlCellTypeLastCell).Row

Do Until lastRow
    Insert cell([3],3) into txtExclusions[1]
Loop
Thanks
Reply With Quote
  #4 (permalink)  
Old June 29th, 2011, 05:32 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation error 1004

I have found some code that is maybe closer to what I want (although I really do not mind which method {see above} I use just as long as the result is achieved):

Code:
 Dim rngLastRow As Range
Dim lastRow As Long
Dim strNextRow As String

    oXB.Sheets(1).Range("B3").Activate
    strID = oXB.ActiveCell.Value
    Do Until strID = "" 'This will loop while there is a value on col 1
        'strID has the value of the first cell
        oXB.ActiveCell.Offset(1, 0).Activate 'go to next row
        strNextRow = oXB.ActiveCell.Value
        
        txtExclusion1.Text = strNextRow
    Loop
However, I get an error on the bold line. The error is 1004 (Acitvate method of range class failed). When I tried using this earlier with the special cells I cannot remember whether it was this error or a different one but it always occurs at the oXB.sheets(1).cells {or at code setting the workbook and sheet}

thanks
Reply With Quote
  #5 (permalink)  
Old June 29th, 2011, 07:35 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation error 6 inserting textboxes

Hi,

As well as the loop (see above) I would like to insert textboxes using loops but using the code below I get a runtime error 6 (overflow) and do not understand why. It did work once (before I edited the code slightly) and inserted way too many (alot more than 5 more like 100!).

Code:
Private Sub btnAddTextbox_Click()
    Dim myTextbox As TextBox
    Dim intTxtNum As Long
    Dim intPost As Long 'The position at which to place the next textbox
    

    intTextNum = 1
    'intPost = 1
    
    Do Until intTextNum = 5
        intTxtNum = intTxtNum + 1
        'intPost = intPost + 30
        
        Set myTextbox = Me.Controls.Add("Forms.TextBox.1", "txtTest" & intTxtNum, Visible)
        
        With myTextbox
            .Left = 42
            .Top = 4
            .Width = 570
            .Height = 44
            .EnterKeyBehavior = True
            .Locked = True
            .MultiLine = True
            .Font.NAME = arial
            .Font.Size = 11
            .ScrollBars = fmScrollBarsVertical
            .Value = "Insert here!"
        End With
    Loop
End Sub
Thanks
Reply With Quote
  #6 (permalink)  
Old June 30th, 2011, 03:28 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

You will have more control using for loop

Here is an example


Code:
 
 For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row
    sFormula = Cells(i1, 2).Formula
    sFormula = Replace(sFormula, "D", "E")
    Cells(i1, 2).Formula = sFormula
 Next i1
Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #7 (permalink)  
Old June 30th, 2011, 06:53 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default

I do not really understand the content. Is the example of the For loop rather than the content of the loop?

Also, how do the last cells know to reference the excel document (I am in word and am transferring data using oXB = [filepath]

And how does it know which column to use

Thanks

Christine

Last edited by cjcant; June 30th, 2011 at 09:34 AM..
Reply With Quote
  #8 (permalink)  
Old June 30th, 2011, 10:09 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Yes! It is just an example of for loop.

Since you have posted couple of questions on the fly, don't know which was the one you had problem with

Can you post the snippet that gives you the problem and what is its intention

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
  #9 (permalink)  
Old June 30th, 2011, 10:31 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default What I want to do:

Sorry, I thought I had information further up telling of what I wanted to do! must have been another thread.

The easiest way for me to explain it is sort of phonetically:
[] = the number goes up by 1 each time it loops
chk = checkboxes
txt = textboxes


Code:
    Dim oXB As Excel.Workbook
 
    Set oXA = Excel.Application
    Set oXB = oXA.Workbooks.Open ("H:\proposalDocumentDevelopment\HVAC\experiment\excel\excelDataMockAmec.xlsx")
    
 'when form opens
    Do Until oXB.sheet(2).column(2).lastRow
        Insert cell([3],3) into txtExclusions[1]
    Loop


'whenever a checkbox is clicked
    Do Until lastTextbox
        If chkExclusions[1].Value = True Then
            txtExclusions[1].add
         'take the data from the textbox and insert into the word document at the cursor
            Selection.Range.InsertAfter = txtExclusions[1].Text
         EndIf
    Loop
If this makes no sense to you let me know and I will try to explain it in a different way. I also am happy to use any loop this was just the way I have it in my head.

Thanks

Last edited by cjcant; July 1st, 2011 at 04:16 AM.. Reason: forgot to include the adding of a textbox
Reply With Quote
  #10 (permalink)  
Old July 2nd, 2011, 03:24 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

So you are trying to loop through the textboxes! Am I right?

You can't go with the index property (TextBox[1]) etc. However you can use the Control's collection

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
excel data into word cjcant Word VBA 10 June 13th, 2011 06:43 AM
Word 2007 shortcut to open the UserForm cjcant Word VBA 1 June 6th, 2011 06:46 AM
VBA Word Userform Dynamically - Help rangeshram Word VBA 9 August 17th, 2009 08:48 AM
Excel UserForm ttitto Excel VBA 0 December 11th, 2007 12:20 PM
open word document and insert data PorcupineRabbit Visual Basic 2005 Basics 1 August 9th, 2007 06:31 PM



All times are GMT -4. The time now is 08:03 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.