How Do I modify this code to look at an external file to populate the dropdown list?

[kwiat]

Hello

I have this code that work with my ribbon control, I have a dropdown box that is populated by the text in the cells from A7:A100 on the active sheet I am on. I want to set it up so it will look at an external file (file can be txt, xls..etc) stored in a folder (does not open it) and pull the values in from that file. The setup below only works when I open this workbook but I am trying to set this up as an add in in a separate tab that will be populated even if no workbooks are open. Can I somehow set the path to the file in this code?


''=========Drop Down Code =========


''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.

Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)


With Sheet1.Range("A7:A100")
Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
ItemCount = ListItemsRg.Rows.Count
returnedVal = ItemCount
End With
End Sub

''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
returnedVal = ListItemsRg.Cells(index + 1).Value
''index is 0-based, our list is 1-based so we add 1.
End Sub

 

[Worf]

Hello

See if this example does what you want:

Sub Main()
    Dim ct As IRibbonControl, i%
    DDItemCount ct, i
End Sub


Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
Dim wr As Range, ListItemsRg As Range, ItemCount%, wb As Workbook


Application.ScreenUpdating = False


' inform full path
Set wb = Workbooks.Open("C:\Windows\Logs\Source.xls")
Set wr = wb.Worksheets("Sheet1").Range("a7:a10")


With wr
    Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
    ItemCount = ListItemsRg.Rows.Count
    returnedVal = ItemCount
    'MsgBox ListItemsRg.Address & vbNewLine & ItemCount & vbNewLine _
    & returnedVal & vbNewLine & wb.Path
End With


wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
End Sub

 

[kwiat]

worf

Thanks for the reply, I have tested it and it does not work. I guess the way this is set up in the code is the real problem. Apparently the code first reads the file and gets the total number on cells that have text in it within the range, then it executed the next doe that many times moving from the first cell to the next one down until it is complete. It also requires the file to be open, if you have it open the file first then for some reason it does not work properly as the file you opened is the active file so I then need to set it up to switch back to the other file. In this code if it closes the file prematurely before it has run the macro for each number of cells with text then it does not complete the list.

here is the full code:

Option Explicit


Dim ItemCount As Integer
Dim ListItemsRg As Range
Dim MySelectedItem As String






''=========Drop Down Code =========


''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.

Sub Main()
Dim ct As IRibbonControl, i%
DDItemCount ct, i
End Sub

Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
Dim wr As Range, ListItemsRg As Range, ItemCount%, wb As Workbook

Application.ScreenUpdating = False

' inform full path
Set wb = Workbooks.Open("C:\files\file.xls")
Set wr = wb.Worksheets("Sheet1").Range("a7:a100")

With wr
Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
ItemCount = ListItemsRg.Rows.Count
returnedVal = ItemCount
'MsgBox ListItemsRg.Address & vbNewLine & ItemCount & vbNewLine _
& returnedVal & vbNewLine & wb.Path
End With

'wb.Close False
'Set wb = Nothing
Application.ScreenUpdating = True
End Sub

''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
returnedVal = ListItemsRg.Cells(index + 1).Value
''index is 0-based, our list is 1-based so we add 1.
'Workbooks("Logfile.XLS").Close SaveChanges:=False
End Sub

''Drop down change handler.
''Called when a drop down item is selected.
Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
' Two ways to set the variable MySelectedItem to the dropdown value

'way 1
MySelectedItem = ListItemsRg.Cells(index + 1).Value

''way 2
'Call DDListItem(control, index, MySelectedItem)

End Sub

''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
returnedVal = 0
MySelectedItem = ListItemsRg.Cells(1).Value
End Sub


''------- End DD Code --------

''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem(control As IRibbonControl)
MsgBox "The variable MySelectedItem have the value = " & MySelectedItem & vbNewLine & _
"You can use MySelectedItem in other code now to use the dropdown value"
End Sub








And here is the xml for the ribbon

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">  <ribbon>    <tabs>
      <tab id="CustomTab" label="OPEN FILES" >
      	<group id="FILES" label="FILES" insertAfterMso="GroupEditingExcel">              		<dropDown id="dd1" 				label="FILES" 				getItemCount="DDItemCount" 				getItemLabel="DDListItem" 				onAction="Macro1" 					getSelectedItemIndex="DDItemSelectedIndex"/>
      	</group>
     
      </tab>
    </tabs>  </ribbon></customUI>

 

[Worf]

Hi</SPAN></SPAN>
My other post was just an example, now I begin to understand your project better. A few questions:</SPAN></SPAN>

• I assume the dropdown box is located inside the Ribbon control, is that correct?</SPAN></SPAN></SPAN>
• The XML code refers to Macro1 at the onAction line, should this be referring to DDOnAction instead?</SPAN></SPAN></SPAN>
• Is it working perfectly when the source range is at the same workbook?</SPAN></SPAN></SPAN>

 

[kwiat]

Worf

below is my answer, not sure if this makes sense of not.

Hi
My other post was just an example, now I begin to understand your project better. A few questions:

• I assume the dropdown box is located inside the Ribbon control, is that correct?

YES

• The XML code refers to Macro1 at the onAction line, should this be referring to DDOnAction instead?

It is just a placeholder, what I am looking for is a macro that will open a specific template based on the selection in the list. for example, if the list has

template A
template B
template C

And template C is selected on the list (there are other criteria needed but for now this is to simplify it), when I select another ICON in the ribbon it will open template C.

• Is it working perfectly when the source range is at the same workbook?

YES, the problem is to make it an add-in then it is just as complex to modify the list in the drop down as it would be if I hard coded it into the ribbon UI. What I foresee is an example where there is a workbook with multiple tabs, the names of the tabs are used to populate the drop down and the worksheet for each said tab is opened. This is the basic parts, once I get this I have other things I need to do like populate the template file with data from a database besed on criteria that is specified in the ribbon like a date range, how the data is displayed..etc

 

[Worf]

Hi

This is not the finished product yet, but already does some interesting stuff. When the workbook is opened, the new Ribbon tab is created; clicking on its name (OPEN FILES) will trigger data transfer from the external file.
Clicking on it again will display the populated drop-down box. Now I´ll make this an add-in.
Please tell me if we are headed on the right direction...

Option Explicit


Dim ItemCount%, ListItemsRg As Range, MySelectedItem$


''=========Drop Down Code =========


''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.


Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
Dim wr As Range, wb As Workbook


Application.ScreenUpdating = False


' inform full path
Set wb = Workbooks.Open(ThisWorkbook.Path & "\source.xlsm")


Set wr = ThisWorkbook.Worksheets("Sheet1").Range("a7:a10")
' transfer data from other workbook
wr.Value = wb.Worksheets("Sheet1").Range("a7:a10").Value


With wr
    Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
    ItemCount = ListItemsRg.Rows.Count
    returnedVal = ItemCount
End With


wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
End Sub


''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.


Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1).Value
    ''index is 0-based, our list is 1-based so we add 1.
    'Workbooks("Logfile.XLS").Close SaveChanges:=False
End Sub


''Drop down change handler.
''Called when a drop down item is selected.


Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
' Two ways to set the variable MySelectedItem to the dropdown value


'way 1
MySelectedItem = ListItemsRg.Cells(index + 1).Value
''way 2
'Call DDListItem(control, index, MySelectedItem)


End Sub


''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value
End Sub


''------- End DD Code --------


''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem()


    MsgBox "The variable MySelectedItem has the value = " & MySelectedItem


End Sub

 

[kwiat]

Not working, it keeps coming bad to debug on this line:

Set wr = ThisWorkbook.Worksheets("Sheet1").Range("a7:a10")

I set the path (Set wb = Workbooks.Open(ThisWorkbook.Path & "\logfile.xls")) to the file which is just "\ logfile.xls" and this files open then hangs up. Secondly, how would I set the full path if it was on another drive or location? I tried that inside the quotes and it hangs there.

Hi

This is not the finished product yet, but already does some interesting stuff. When the workbook is opened, the new Ribbon tab is created; clicking on its name (OPEN FILES) will trigger data transfer from the external file.
Clicking on it again will display the populated drop-down box. Now I´ll make this an add-in.
Please tell me if we are headed on the right direction...

Option Explicit


Dim ItemCount%, ListItemsRg As Range, MySelectedItem$


''=========Drop Down Code =========


''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.


Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
Dim wr As Range, wb As Workbook


Application.ScreenUpdating = False


' inform full path
Set wb = Workbooks.Open(ThisWorkbook.Path & "\source.xlsm")


Set wr = ThisWorkbook.Worksheets("Sheet1").Range("a7:a10")
' transfer data from other workbook
wr.Value = wb.Worksheets("Sheet1").Range("a7:a10").Value


With wr
    Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
    ItemCount = ListItemsRg.Rows.Count
    returnedVal = ItemCount
End With


wb.Close False
Set wb = Nothing
Application.ScreenUpdating = True
End Sub


''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.


Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1).Value
    ''index is 0-based, our list is 1-based so we add 1.
    'Workbooks("Logfile.XLS").Close SaveChanges:=False
End Sub


''Drop down change handler.
''Called when a drop down item is selected.


Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
' Two ways to set the variable MySelectedItem to the dropdown value


'way 1
MySelectedItem = ListItemsRg.Cells(index + 1).Value
''way 2
'Call DDListItem(control, index, MySelectedItem)


End Sub


''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value
End Sub


''------- End DD Code --------


''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem()


    MsgBox "The variable MySelectedItem has the value = " & MySelectedItem


End Sub

 

[Andrew Poulsom]

If you want to use an Add-In why can't you populate your dropdown from a worksheet in that Add-In?

 

[kwiat]

If you want to use an Add-In why can't you populate your dropdown from a worksheet in that Add-In?

I can but this project is a little more complex. I am building a series of files that will be used as templates, the names of the templates will be what populates the drop down list. Now I can create a worksheet add in that has all the templates on individual worksheets and then the list of each worksheet on another tab to be used in the dropdown. The problem lies in where not all users will use the exact same set of templates. I have thought of building a configuration macro where the workbook is opened, modified by a user or administrator and saved back as the add-in, this might be one option and seems simplest but can I do that? When I try to open an excel add-in workbook it does not open the worksheets just excel without any worksheets opened.

this project is based on reviewing data collected from machines. For instance, say I have

Machine A, Machine B, Machine C, Machine D, Machine E, Machine F (each machine has a template file in a workbook, if the machine are the same but there are two or more the same template would be used but populated with the data from the proper machine)

One user might only need access to Machines A, B & F, Another A, B, C & F and another only C & D

I was originally looking for a configuration folder where the files are stored containing the templates only they require and the list of the names for each template (or maybe the dropdown can be populated by the sheet names?). The Add-in would be active in Excel for them at all times, when they went to the OPEN FILE tab the dropdown list would be populated with the names of the available templates (Machines A, B & F). When they select the machine they want to review data from in the list they would execute the macro that would open the template and populate it with the data from that machine. Additional details on the data would be date and time so only specific data would be displayed. Basically I am building a filter system for data.

 

[Andrew Poulsom]

To see the worksheets in an Add-In set its IsAddin property to False. Set it back to True before saving it.