Loop to insert excel data into word userform

cjcant · June 28th, 2011, 11:11 AM

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

Shasur · June 28th, 2011, 01:20 PM

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

cjcant · June 29th, 2011, 04:32 AM

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

cjcant · June 29th, 2011, 05:32 AM

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

cjcant · June 29th, 2011, 07:35 AM

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

Shasur · June 30th, 2011, 03:28 AM

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

cjcant · June 30th, 2011, 06:53 AM

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

Shasur · June 30th, 2011, 10:09 AM

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

cjcant · June 30th, 2011, 10:31 AM

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

Shasur · July 2nd, 2011, 03:24 AM

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