Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic dropdown list or radio buttons (All)

    Hi

    I have question for all Excel gurus - I know WORD a bit but not EXCEL. Perhaps you guys can suggest if this can be done:

    In Excel I have a list of items (several columns). This is what I would like to do using a macro or any other ways if it simpler:

    From the user, get the column number to process. I am not sure how to do this - either by a list of dynamic radio buttons or an expanding dropdown list. Therefore, if the user selects radio button 1 or item 1 from the drop down list, evaluate column 1 of the list and take action for the row & so on. The radio buttons method or dropdown list method has to be flexible and be expandable dynamically, if more columns are added to the list. The process would be as follows & I am thinking of doing it as a macro:

    1. Get the column number to process from the user (as described above)
    2. Select the whole worksheet or all the items in the list (not sure how to find out how many rows are filled in a worksheet) & unhide all rows/columns
    3. For each row in the worksheet
    Evaluate selected column
    If column is set to 'Y',
    copy selected columns [say from col 4 to 10] to another sheet, i.e. create another table
    If column is NOTset to 'Y' ,
    hide the current row
    4. When all rows processed, import the copied table into a WORD document

    I don't know if this is possible in WORD. Perhaps better to use WORD? Or may be you guys have better idea/method/way of achieving the same. All, I want is to import selected row (with selected columns) into a WORD document. If this can be done easily in WORD then I would go it compared to Excel.

    Thanks in advance.

    Robie
    Thanks.
    Robie

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    I am a little confused at what you want and have. Could you attach a sample of what you have and some idea of what the results should be and elaborate on what the user input would be?

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Thanks for the response Steve & apologies for the confusion.

    It's very simple really. I will break it down in several requirements.

    1. Show/Hide all rows in the current worksheet
    2. a. A dropdown list that is populated at run-time (i.e. at start of macro) or b. Ability to create either a userform with radio buttons that can be increased at run-time. I was thinking that maybe I would define values in custom properties to say, e.g. number of radio button/items in the drop down list, description of each item etc?
    3. Hide / Show the rows in the Excel table based on the column information.
    For example, if the user selects radio button 1 or item 1 from the dropdown list: for each row - if the column 1 is NOT set to 'Y' - hide that row. If the user selects radio button 2 or item 2 from the dropdown list: for each row - if the column 2 is NOT set to 'Y' - hide that row & so on.
    4. Once I have process the worksheet - copy the remaining displayed items in the Excel table (i.e. some rows would be hidden) into a WORD document

    Hope this is clear. Unfortunately, I am unable to attach anything here - I can only do it @ home.

    Thanks

    Robie
    Thanks.
    Robie

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Perhaps you don't need complicated code. Using AutoFilter, the user can easily filter all rows containing "Y" in a specified column, then copy the desired range, and paste it into Word. Only the filtered rows will be copied.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Thanks Hans. Your idea looks promising. I will try it out.

    Since you are a WORD expert as well <img src=/S/smile.gif border=0 alt=smile width=15 height=15> , can this or something similar be done within a WORD table? If need be, we could add VBA if necessary.

    Thanks

    Robie
    Thanks.
    Robie

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    I think it would be much more complicated in Word.

    I have attached a very simple example in Excel.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Thanks Hans. I think this may be good option for me.

    Now another question: Once this Excel 'table' is generated - how do I import it into WORD? What I mean is my users are not very good at this sort of thing and what I would like to do is automate some of the tasks for them.

    Is it possible to import just some of the columns (say from columns 5 to 20)? If I use Insert | File it inserts everything. If I use Insert | object it inserts it as an object and it is not possible for it to go over multiple pages.

    Thanks.

    Robie
    Thanks.
    Robie

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Simply select the cells you want to transfer to Word, then copy them to the clipboard (Ctrl+C).
    Activate the Word document where you want the data, click where you want them, and either paste (Ctrl+V) or use Edit | Paste Special... - there are several options such as formatted text, unformatted text and Excel worksheet object. Formatted text is probably the best option.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Here is a version of the workbook with some command buttons added. The bottom one will attempt to copy/paste some columns into the currently active Word document.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Thanks Hans. I will try these out tonight at home - unfortunately my company will not allow me to download anything here (even if it is work related - duh!).
    Thanks.
    Robie

  11. #11
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Thanks Hans. I tried this out last night. Looks good. Thanks once again.

    Once final question: What this means is that whenever the Excel spreadsheet changes - the user will have to delete the imported Execel table in the Word document and then redo the copy/paste process. Is there any way of updating the table in WORD without going through the deletion etc? Probably not - I thought I wuold just ask.

    Thanks for all your efforts in solving my problem. I am so grateful to this forum and its members.

    Robie
    Thanks.
    Robie

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    It is possible paste a link to an Excel table into Word instead of simply pasting it. Manually, you do this by selecting Edit | Paste Special in Word and clicking the Paste Link radio button. In the code in the workbook I attached yesterday, simply change

    app.Selection.PasteExcelTable LinkedToExcel:=False, _
    WordFormatting:=False, RTF:=True

    to

    app.Selection.PasteExcelTable LinkedToExcel:=True, _
    WordFormatting:=False, RTF:=True

    This will make the pasted table in Word update itself automatically when the Excel table changes (for example if the filter is changed). You can control how the updating is done by selecting Edit | Links in Word.

  13. #13
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    Hans

    I have been using your ideas very successfully so far. Thanks.

    I have a question regarding the number of rows copied to the WORD document? Is there any way we only copy the amount of rows displayed? For example, if the Excel table content is increased by few lines - how can we copy those extra rows into Word without 'importing' the blank rows after the end of data? If I am copying 100 rows but the actual data displayed is just 46 rows then blank rows are also copied into the word document? The EXCEL table is dynamic and will have more data added to it as it goes along.

    Your code - I increased the number of rows copied to 1000 from 20 - so that all available data is copied into word - but it also copies blank lines?
    Sub Paste2Word()
    Dim app As Object
    Range("D1:E1000").Copy
    On Error Resume Next
    Set app = GetObject(Class:="Word.Application")
    If app Is Nothing Then
    MsgBox "Word is not active.", vbExclamation
    Exit Sub
    End If
    app.Selection.PasteExcelTable LinkedToExcel:=True, _
    WordFormatting:=False, RTF:=True
    app.Activate
    End Sub

    Thanks

    Robie
    Thanks.
    Robie

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic dropdown list or radio buttons (All)

    You could define a dynamic named range in Excel, and create a link to this named range.

    For example, assume that all cells from D1 to the last used cell in column D are filled (non-empty).
    Select Insert | Name | Define...
    Specify a name, for example MyRange.
    Set Refers To to the formula

    =OFFSET(SheetName!$D$1,0,0,COUNTA(SheetName!$D:$D) ,2)

    Click OK.

    Next, change the instruction
    <code>
    app.Selection.PasteExcelTable LinkedToExcel:=True, _
    WordFormatting:=False, RTF:=True
    </code>
    to
    <code>
    app.ActiveDocument.Fields.Add Range:=app.Selection.Range, _
    Text:="LINK Excel.Sheet.8 " & Chr(34) & Replace(ActiveWorkbook.FullName, "/", "//") & _
    Chr(34) & " MyRange a r"
    </code>
    HTH

Posting Permissions

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