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 Display Modes
  #11 (permalink)  
Old July 7th, 2011, 10:07 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation

Yes, but it is more than that. I want to:
  1. Retrieve data from several cells (from one column) in excel using a loop so that once it reaches the last filled cell the loop stops. I know this is possible in excel but have not yet found a way to do it from Word.
  2. Then add a new textbox (and checkbox) for every cell (that contains data)
  3. Insert the data into those textboxes (in the userform).
  4. If a checkbox is selected then the assosiated textbox data is inserted into the word document where the cursor is (so using the selection method)

The main parts I want to be able to do is 1 and 4 but 2/3 are also fairly important as it would cut down on time massively.

Thanks
Reply With Quote
  #12 (permalink)  
Old July 10th, 2011, 12:22 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
Thumbs up

  1. Retrieve data from several cells (from one column) in excel using a loop so that once it reaches the last filled cell the loop stops. I know this is possible in excel but have not yet found a way to do it from Word.
The same mechanism should work in Word too. Since you are using the Excel object , just prefix with the appropriate object reference
  1. Then add a new textbox (and checkbox) for every cell (that contains data)
Hope this textbox is added to the form. You can add that using Controls. Add
  1. Insert the data into those textboxes (in the userform).
Same as above - Controls.Add
  1. If a checkbox is selected then the assosiated textbox data is inserted into the word document where the cursor is (so using the selection method)
This will be in Button Click event

But beware if there are more than say 40 items in the Workbook your form will look clumsy. Can you better try with some Grid on userform .. or a List of Checkboxes (not sure if the latter is available in VBA - it is in .NET) - Grid is available

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

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Reply With Quote
The Following User Says Thank You to Shasur For This Useful Post:
cjcant (July 11th, 2011)
  #13 (permalink)  
Old July 12th, 2011, 07:58 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default retrieve data from textbox

Thanks

That is great! I can now retrieve data from excel. I am now wondering how to retrieve data from the textboxes using loops. I can get the loop but the if statement is causing problems as I am trying to do:

Code:
For intI = 1 To LastRow
        'set values
        intControl = intControl + 1
        If Controls("chkExclusio" & intControl).Value = True Then 
           rngExcelTest.Text = "Boo!" '("txtExclusio" & intControl)
            ''Re-insert the bookmark
            ActiveDocument.Bookmarks.Add "excelTest", rngExcelTest
        End If
The bit in bold is causing problems as I am not sure how to select each checkbox in turn. (checkboxs named txtExclusio1, txtExclusio2, txtExclusio3, etc) The error appearing is "cannot find the specified object".

Thanks

Last edited by cjcant; July 12th, 2011 at 08:15 AM.
Reply With Quote
  #14 (permalink)  
Old July 12th, 2011, 08:28 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default

This is the code within context if you want it especially as the if statement seems to work ok. The bit in bold only causes a problem when the loop reaches the last cell.

Code:
Private Sub UserForm_Activate()

    Dim oXA As Excel.Application
    Dim oXB As Excel.Workbook
 
    Set oXA = Excel.Application
    Set oXB = oXA.Workbooks.Open("H:\proposalDocumentDevelopment\proposalTemplates\ScopeDeliverablesExclusionsExcelData.xlsx")


    Dim I As Integer 'loop count
    Dim LastRow As Integer 'count the number of rows that have something in them
    Dim myTextbox As MSForms.TextBox 'add textbox
    Dim myCheckbox As MSForms.CheckBox 'add checkbox
    Dim intControl As Integer 'to attach number to the textbox/checkbox name
    Dim intTop As Integer 'to place the textbox at a certain height
    Dim intScrollHeight As Integer 'to extend the form height
    
    LastRow = oXB.Sheets(2).Cells(Rows.Count, 3).End(xlUp).Row
    intControl = 0
    intTop = -24
    intScrollHeight = 575
    
    'loop until last row
    For I = 3 To LastRow
        'set values
        intControl = intControl + 1
        intTop = intTop + 60
        
        'if the textboxes pass the end of the userform extend the height
        If intControl > 9 Then
            UserForm3.ScrollHeight = intScrollHeight + 105
        End If
        
        'add textbox with properties (including inserting cell data into {value})
        Set myTextbox = UserForm3.Controls.Add("Forms.TextBox.1", "txtExclusio" & intControl)
        With myTextbox
            .Value = Range("C" & I)
            .Height = 44.25
            .WordWrap = True
            .Left = 42
            .Top = intTop
            .Width = 570
            .ScrollBars = fmScrollBarsVertical
            .Locked = True
            .MousePointer = fmMousePointerNoDrop
        End With
        
        
        'add checkbox with properties (including inserting cell data into {value})
        Set myCheckbox = UserForm3.Controls.Add("Forms.CheckBox.1", "chkExclusio" & intControl)
        With myCheckbox
            .Height = 21.75
            .Left = 24
            .Top = intTop
            .Width = 580
            .BackStyle = fmBackStyleTransparent
        End With
        
    Next I

    'release the excel document
    Set oXB = Nothing
End Sub

Private Sub btnInsert_Click()
    Dim oXA As Excel.Application
    Dim oXB As Excel.Workbook
    Set oXA = Excel.Application
    Set oXB = oXA.Workbooks.Open("H:\proposalDocumentDevelopment\proposalTemplates\ScopeDeliverablesExclusionsExcelData.xlsx")

    Dim intI As Integer 'loop count
    Dim LastRow As Integer 'count the number of rows that have something in them
    Dim intControl As Integer 'to attach number to the textbox/checkbox name

    LastRow = oXB.Sheets(2).Cells(Rows.Count, 3).End(xlUp).Row
               
    Dim rngExcelTest As Word.Range
    Set rngExcelTest = ActiveDocument.Bookmarks("excelTest").Range
    
    intControl = 0
    intI = 1
    
    'loop until last textbox
    For intI = 1 To LastRow
        'set values
        intControl = intControl + 1
        If Controls("chkExclusio" & intControl).Value = True Then
            rngExcelTest.Text = "Boo!" '("txtExclusio" & intControl)
            ''Re-insert the bookmark
            ActiveDocument.Bookmarks.Add "excelTest", rngExcelTest
        End If
    
    Next intI
End Sub

Last edited by cjcant; July 12th, 2011 at 09:25 AM.
Reply With Quote
  #15 (permalink)  
Old July 12th, 2011, 09:29 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Actually problem is solved. I was being stupid and hadn't set intI = 3 (the excel data starts on the 3rd row)

Thanks
Your help has been greatly appreciated

Last edited by cjcant; July 12th, 2011 at 10:32 AM. Reason: solved!
Reply With Quote
  #16 (permalink)  
Old July 12th, 2011, 10:33 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

Glad to hear that it worked.

Also nice to see you picking up very fast. Good show!

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

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

Thanks but I would not have been able to do it without help.

Thanks
Reply With Quote
  #18 (permalink)  
Old July 20th, 2011, 01:23 PM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation error - Could not find specified object

Hi,

I realised I have come up with another error. However, this one only occurs in the loop the second time the form is opened. (I have marked below where the error occurs.) The error is "Could not find specified object" (the error number is the mega long one)

The bookmark I am inserting into does dissappear. I think the problem is that it is counting the textboxes which we have added even though they are not visible. Is there any way I can delete all the textboxes I added (using code)? There is one textbox that was added in design mode and it is required to stay.

Code:
  Dim intI As Integer 'loop count
    Dim intLastBox As Integer 'count the number of textboxes
    Dim intControlInsert As Integer 'to attach number to the textbox/checkbox name
    Dim strTextboxSingleData As String 'data shown within textbox
    Dim strInsertData As String
    Dim ctrlTextbox As Control
     
    intLastBox = 0
    'strTextboxSingleData = ""
    
    For Each ctrlTextbox In Me.Controls
        If TypeOf ctrlTextbox Is MSForms.TextBox Then
            intLastBox = intLastBox + 1
        End If
    Next
    
    intLastBox = intLastBox - 1 'there is an extra textbox for the search
    intControlInsert = 0
    
    'loop until last textbox
    For intI = 1 To intLastBox
        'set values
        intControlInsert = intControlInsert + 1
        'strTextboxSingleData = Me.Controls("txtDEBox" & intControlInsert).Value
        
        'if checkbox is selected
       
Quote:
If Me.Controls("chkDEBox" & intControlInsert).Value = True Then strInsertData = strInsertData & Me.Controls("txtDEBox" & intControlInsert).Value & vbNewLine & vbNewLine
rngDFATIntro.Text = strInsertData ActiveDocument.Bookmarks.Add "bmDFATIntro", rngDFATIntro With rngDDocumentation .Style = "Normal" .ParagraphFormat.IndentCharWidth (1) End With End If Next intI intControlInsert = 0 intI = 0 For intI = 1 To intLastBox intControlInsert = intControlInsert + 1 If Controls("chkDEBox" & intControlInsert).Value = True Then Controls("chkDEBox" & intControlInsert).Value = False End If Next intI
Please help!

Thanks

Last edited by cjcant; July 21st, 2011 at 04:57 AM. Reason: figured out problem but need solution
Reply With Quote
  #19 (permalink)  
Old July 21st, 2011, 07:08 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi,

Just to let you know I have solved it. It was because the textboxes were not being deleted (just hidden). So what I did was just unloaded then reloaded the form. I know this is not the most efficent way but it works!
Reply With Quote
Reply


Thread Tools
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 07:43 AM
Word 2007 shortcut to open the UserForm cjcant Word VBA 1 June 6th, 2011 07:46 AM
VBA Word Userform Dynamically - Help rangeshram Word VBA 9 August 17th, 2009 09: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 07:31 PM



All times are GMT -4. The time now is 12:56 AM.


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