From: Masaki Suketa <masaki.suketa@...>
Date: 2007-09-30T07:10:46+09:00
Subject: Re: OLE Object - combo box problem

Bulhac Mihai wrote:
> tnx a lot, now additem its working, but for the combo box menu make it 
> work (options to be links to another worksheet) i dont want tu use ruby, 
> i want that with ruby script to record a macro and that macro is called 
> when i select a option from the combo box because my ruby script is 
> closed when i'm reading the excel file; is that possible?

I'm not sure that I understand what you want to do,
But does the following script help you?

require 'win32ole'
ex = WIN32OLE.new('Excel.Application')
ex.visible = true
book = ex.workbooks.add
sheet = book.worksheets(1)
combo = sheet.OLEObjects.Add("Forms.Combobox.1").object
oles = sheet.OLEObjects
book.worksheets.each do |sh|
   combo.additem(sh.name)
end
modu = book.VBProject.VBComponents.Item(sheet.name)
modu.CodeModule.InsertLines(1, "Private Sub " + combo.name + "_Change")
modu.CodeModule.InsertLines(2, " MsgBox " + combo.name + ".Text" )
modu.CodeModule.InsertLines(3, "End Sub")