combobox or listboxes in macro

m-scheiber

Dear all,

In my company we would like to use the on new macro for predefined part creation.
Currently we ask a lot of information from the new part via messageboxes in the onNew macro.
It would be a lot more user friendly if some of these messageboxes could be converted to list or combo boxes.

e.g.
current inputbox =
("selec type" & vbcrlf & "1) spare part" & vbcrlf & "2) machine" & vbcrlf & "3) subassembly" & ....
Then the user has to type 1 or 2 or 3 in the inputbox and then we have the part type info.

I would prefer to have a listbox that the user can scroll and select 'Spare part' or 'Machine'.

Does anyone has an idea to accomplish that via the macro?

I know that there is a possiblity to create a visual basic programma and to call this and then run everything but via macro you can stay in Visual.

unknown

There are a couple of ways to display a combo box or listbox on screen from a Visual macro. If you have access to the VMIUG-TEC group on Yahoo, you will see several examples that I created that use either an on-the-fly built web page in Internet Explorer or an Excel userform:
tech.groups.yahoo.com/.../
VMPRTMNT_AddPaintPart.zip (self-contained pre-created HTML page), VMPRTMNT_IEDataEntry.zip (using an ActiveServer page)

If you download my June 2009 presentation titled "Making the Most of Visual Licenses, the SQL - Using Visual Macros, Excel Macros, ADO, and WMI" you will see examples that access an Excel userform:
tech.groups.yahoo.com/.../
VMPRTMNT_BrowseWindow.vms, VMPRTMNT_BrowseWindow2.vms, VMPRTMNT_ExcelDataEntry.vms (Userform in Excel), VMPRTMNT_IEDataEntry3.vms (comboboxes), VMPRTMNT_IEDataEntry2.vms (option/radio buttons), VMPRTMNT_IEDataEntry.vms (text boxes and checkboxes).

I am not sure if this code will post, but this is VMPRTMNT_IEDataEntry3.vms:
'Created by Charles M. Hooper
'May 21, 2009
'This macro script may be used/modified as needed

Dim objIE
Dim objShell
Dim strHTML
Dim intFlag

On Error Resume Next

Set objShell = CreateObject("WScript.Shell")

strHTML = strHTML & "" & vbCrLf
strHTML = strHTML & "" & vbCrLf


strHTML = strHTML & "" & vbCrLfstrHTML = strHTML & "

Component

" & vbCrLf strHTML = strHTML & "ACTIVATOR" & vbCrLf strHTML = strHTML & "ACCELERATOR" & vbCrLf strHTML = strHTML & "CATALYST" & vbCrLf strHTML = strHTML & "EPOXY PRIMER" & vbCrLf strHTML = strHTML & "PRIMER" & vbCrLf strHTML = strHTML & "REDUCER" & vbCrLf strHTML = strHTML & "TOP COAT" & vbCrLf strHTML = strHTML & "

" & vbCrLf

strHTML = strHTML & "

Vendor

" & vbCrLf strHTML = strHTML & "DUPONT" & vbCrLf strHTML = strHTML & "LILLY" & vbCrLf strHTML = strHTML & "NILES CHEMICAL" & vbCrLf strHTML = strHTML & "MANITOWOC" & vbCrLf strHTML = strHTML & "MAUTZ" & vbCrLf strHTML = strHTML & "PAINTS AND SOLVENTS" & vbCrLf strHTML = strHTML & "SHEBOYGAN" & vbCrLf strHTML = strHTML & "

" & vbCrLf

strHTML = strHTML & "

Type	" & vbCrLf strHTML = strHTML & "FLAT" & vbCrLf strHTML = strHTML & "GLOSS" & vbCrLf strHTML = strHTML & "MED. GLOSS" & vbCrLf strHTML = strHTML & "

" & vbCrLf

strHTML = strHTML & "

Color

" & vbCrLf strHTML = strHTML & "RED" & vbCrLf strHTML = strHTML & "YELLOW" & vbCrLf strHTML = strHTML & "GREEN" & vbCrLf strHTML = strHTML & "BLUE" & vbCrLf strHTML = strHTML & "BLACK" & vbCrLf strHTML = strHTML & "WHITE" & vbCrLf strHTML = strHTML & "GRAY" & vbCrLf strHTML = strHTML & "

" & vbCrLf
strHTML = strHTML & "" & vbCrLf

strHTML = strHTML & "

" & vbCrLf
strHTML = strHTML & "

" & vbCrLf

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"
objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title="Get Part Info"
objIE.Left = 0
objIE.Top = 0
objIE.Width = 400
objIE.Height = 400
objIE.Statusbar = False
objIE.Menubar = False
objIE.Toolbar = False

objIE.Visible = True

Do While objIE.Busy <> False
objShell.Sleep 200
Loop

intFlag = 0

'loop until the button is clicked
Do While intFlag = 0
If Err <> 0 Then
IntFlag = -1
End If
If objIE is Nothing Then
'User closed ID
intFlag = -1
Else
If objIE.Document.All.txtOK.Value <> " " Then
intFlag = 1
End If
End If
'objShell.Sleep 250 'Throws an error?
Loop

If intFlag = 1 Then
'Copy in the values from the web page
USER_1 = objIE.Document.Body.All.cboComponent.Value
USER_2 = objIE.Document.Body.All.cboVendor.Value
USER_3 = objIE.Document.Body.All.cboType.Value
USER_4 = objIE.Document.Body.All.cboColor.Value

objIE.Quit
End If

Set objIE = Nothing
Set objShell = Nothing

Charles Hooper
hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.