Plotting Log

I am pretty new to VBA and am trying to create a plotting log and have a few questions. The purpose of my app is to write an entry into Excel with all of the options I select from a series of combobox controls.

Question 1:
The follow string of code works but if I pull down on the combobox arrow and click it again to make it come come back to normal then pull down again the number multiplies.

Private Sub cmbBill_DropButtonClick()
cmbBill.AddItem "Yes"
cmbBill.AddItem "No"
End Sub

Example:
First Click:
Yes
No

Second Click
Yes
No
Yes
No

And so on. Very frustrating.

Next Question:
How do I get a combobox to pull the list from my system printers. The follow code probably needs refining.

Private Sub cmbPlotter_DropButtonClick()
Dim PlotDev As Object
PlotDev = objPrinter.GetPlotDeviceNames
cmbPlotter.AddItem varPlotDev
'cmbPlotter.AddItem "HP 1055CM"
'cmbPlotter.AddItem "HP LaserJet 5000N"
'cmbPlotter.AddItem "HP InkJet 2300"
End Sub

Any help would be greatly appreciated.

 

Hi Stuck (I'll assume),

Question 1:
Your code is in the wrong place. You don't want to add items to the combo
box each time you click the button -- instead what you need to do is add
items to the combo box when the form is being initialize. Move your two
lines of code into the Form's INITIALIZE event.

Next Question:
I added/changed the lines marked with **. Your code needs to loop through
the array of plot devices and AddItem them one at a time.

** Dim varPlotDev As Object, i as Integer

** for i = lbound(varPlotDev) to ubound(varPlotDev)
** cmbPlotter.AddItem varPlotDev(i)
** next 'i

James

 

Sorry, again because I am new. How would the loop look?

 

That's ok... I've already included the loop that I had mentioned. It's the
FOR...NEXT part of the code. And.... since I didn't run the code after I
typed it, I see I forgot to change your Dim line correctly. Replace the DIM
statement with:

Dim varPlotDev As Variant, i as Integer

Also, try adding this line right before the AddItem statement. It will
print out the names into the "Immediate" window in the VBA editor for you to
read.
Debug.Print "Printer #" & i & ": " & varPlotDev(i)

Lastly, objPrinter needs to be set to something meaningful somewhere else in
your project. What is it defined as? I mean, do you have a "Set objPrinter
= " statement somewhere else in your project?

James

 

Actually, here is the project.


Public Sub PlotLog()
frmPlotLog.Show
End Sub
Private Sub btnCancel_Click()
End
End Sub
Private Sub cmbBill_DropButtonClick()
cmbBill.AddItem "Yes"
cmbBill.AddItem "No"
End Sub
Private Sub cmbProjNo_DropButtonClick()
With cmbProjNo
.AddItem "0001"
.AddItem "0002"
.AddItem "0003"
.AddItem "0004"
.AddItem "0005"
.AddItem "0006"
.AddItem "0007"
End With
End Sub
Private Sub cmbLayout_DropButtonClick()
Dim acLayout As AcadLayout
For Each acLayout In ThisDrawing.Layouts
cmbLayout.AddItem acLayout.Name
Next
End Sub
Private Sub cmbColor_DropButtonClick()
cmbColor.AddItem "Color"
cmbColor.AddItem "B&W"
End Sub
Private Sub cmbPlotter_DropButtonClick()
Dim varPlotDev As Object, i As Integer
varPlotDev = objPrinter.GetPlotDeviceNames
For i = LBound(varPlotDev) To UBound(varPlotDev)
cmbPlotter.AddItem varPlotDev(i)
Next 'i
End Sub
Private Sub cmbMedia_DropButtonClick()
cmbMedia.AddItem "Bond"
cmbMedia.AddItem "Vellum"
End Sub
Private Sub cmbQuality_DropButtonClick()
cmbQuality.AddItem "Draft"
cmbQuality.AddItem "Normal"
cmbQuality.AddItem "Best"
End Sub
Private Sub cmdUpdate_Click()
Dim excel As Object
Dim excelsheet As Object
Dim exapp As Object

On Error Resume Next
Me.Hide
Set excel = GetObject(, "Excel.application")
If Err <> 0 Then
Err.Clear
Set excel = CreateObject("Excel.application")
If Err <> 0 Then
MsgBox "Could not load Excel.", vbOKOnly + vbInformation, "Error report"
End
End If
End If

On Error GoTo 0
excel.Visible = False
Set exapp = excel.workbooks.Open("c:\Temp\DrawingLog\Drawinglog.xls")

DWGNAME = ThisDrawing.Name
DWGPATH = ThisDrawing.Path
DWGBILL = cmbBill.Value
DWGPROJNO = cmbProjNo.Value
DWGCOPY = txtCopy.Value
DWGLAY = cmbLayout.Value
DWGCOLOR = cmbColor.Value
DWGPLOT = cmbPlotter.Value
DWGSIZE = cmbSize.Value
DWGMEDIA = cmbMedia.Value
DWGQUAL = cmbQuality.Value

todaysdate = Date
rownum = 2
columnnum = 1

Set excelsheet = excel.ActiveWorkbook.Sheets("PlotLog")
excel.Sheets("PlotLog").Select

With exapp
Do Until excelsheet.Cells(rownum, 1).Value = "" Or excelsheet.Cells(rownum, 1).Value = DWGNAME
rownum = rownum + 1
Loop

excelsheet.Cells(rownum, 1).Value = DWGPATH + "\" + DWGNAME
If excelsheet.Cells(rownum, 1).Value = DWGNAME Then
columnnum = columnnum + 1
End If

Do Until (excelsheet.Cells(rownum, columnnum).Value = "" Or excelsheet.Cells(rownum, columnnum).Value = todaysdate)
columnnum = columnnum + 1

excelsheet.Cells(rownum, 3).Value = DWGBILL
excelsheet.Cells(rownum, 4).Value = DWGPROJNO
excelsheet.Cells(rownum, 5).Value = DWGCOPY
excelsheet.Cells(rownum, 6).Value = DWGLAY
excelsheet.Cells(rownum, 7).Value = DWGCOLOR
excelsheet.Cells(rownum, 8).Value = DWGPLOT
excelsheet.Cells(rownum, 9).Value = DWGSIZE
excelsheet.Cells(rownum, 10).Value = DWGMEDIA
excelsheet.Cells(rownum, 11).Value = DWGQUAL

Loop
excelsheet.Cells(rownum, columnnum).Value = todaysdate
excel.ActiveWorkbook.Save
excel.ActiveWorkbook.Close
excel.Quit
End With

End Sub
Private Sub btnAbout_Click()
frmPlotLog.Hide
frmAbout.Show
End Sub