A couple of other things:
You are aware that lines like:
Public QS, Quoter, NewBook As Excel.Workbook
actually only give you NewBook as a WB. All the other variables are
variants, essentially untyped.
That's why you can get syntax help when you type "NewBook" + ".", but
nothing with QS or Quoter.
Why not create a QuoteSummary as a template, so you do not have to create it
every time.
You do not need the FSO just to check if a file exists; use GetAttr() or one
of the other native VBA methods.
Always use Option Explicit to force declaration of variables.
Using GetObject does not guarantee you which instance of Excel you receive a
reference to, if you have more than 1 instance running at the time. So I
suppose in theory, oExcel may be completely different from Application,
which would (I assume) not be what you are expected.
Look into using named ranges. This makes it much easier to refer to cell(s).
If you move them on the WS, you not have to update you code to reflect these
change.
I've tried to clear up your code, but there is much that I don't understand
what you trying to achieve and some that is completely invalid e.g.
Range("m17..r17").
NickHK
Arif Ali said:
Nick,
1. Bookname is variable I created. May No longer be needed.
2. THe places where I expect WB's to be closed are where I open, or create
a new, WB and then close it. The simplest example when the user clicks the
ViewQuoteSummaryButton, the InitQuoteSummary routine opens quotesummary.xls,
populates a listbox, and close quotesummary.xls. Then the listbox is shown
until they close it.
3. The initquotesummary could be renamed since its purpose changed slightly
since it was created. It could be called populate_QS_List.
4. Fair enough about the confusion...ThisWorkbook.oExcel was put in since
oExcel is defined in ThisWorkbook section of the project, whereas some of the
subroutines are part of the UserForm object.
Barring any revelations you might have, I will probably strip down this code
and remove all WB-related subs, clean up the code, and then add them back in
one at a time. This program has evolved over time and could use a little
cleanup.
Arif
:
Option Explicit
Public QSPath As String
Public QS As Workbook, NewBook As Workbook
Public FactorySuite As Worksheet, QuoteForm As Worksheet, OrderEntry As
Worksheet
Private Sub Workbook_Open()
Dim RetVal As Variant
Set QuoteForm = Thisworkbook.Sheets("QuoteForm")
Set OrderEntry = Thisworkbook.Sheets("OrderEntry")
Set FactorySuite = Thisworkbook.Sheets("FactorySuite")
QSPath = GetMyDocPath1 ' Set QuoteSumary Path"
On Error Resume Next
RetVal = GetAttr(QSPath & "\QuoteSummary.xls")
If Err.Number > 0 Then
Call CreateQuoteSummary
End If
On Error GoTo 0
With QuoteForm
If (.Range("b24").Value <> "") Or (.Range("c15").Value <> "") Or
(.Range("M15").Value <> "") Then
RetVal = MsgBox("Erase Existing Quote Information?", vbYesNo)
If RetVal = vbYes Then
'This "m17..r17" is invalid. I don't know which range you mean
'.Range("m17..r17").Value = CDate((Now()))
'Does this ("b24..r44") mean all cells between B24 & R44 ?
'Anyway
'Clear data
.Range("C15:E18").ClearContents
.Range("I15:I18").ClearContents
.Range("G18").ClearContents
End If
End If
End With
'What do this achieve ?
With FactorySuite
.Range("A1").Select
End With
'Put this in the UserForm_Initialize events
'UserForm1.MultiPage1.Style = fmTabStyleTabs
'UserForm1.Show 0
'UserForm1.Left = 560
'UserForm1.Top = 30
End Sub
'Make a template so you do not nedd this
Private Sub CreateQuoteSummary()
ScreenUpdating = False
With Workbooks.Add
With .Sheets("Sheet1")
.Range("A1").Value = "Salesman"
.Range("B1").Value = "Quote Date"
.Range("C1").Value = "Customer"
.Range("D1").Value = "Quote Num"
.Range("E1").Value = "Quote Amt"
.Range("F1").Value = "FileName"
.Name = "Quotes"
End With
.SaveAs QSPath & "QuoteSummary.xls"
.Close savechanges:=False
End With
ScreenUpdating = True
End Sub
'Is frmQuoteSummary a UserForm ?
'In which workbook ?
'If in "QuoteSummary.xls" then this routine is meaningless, because you just
open then close the WB
'If in another WB, then the [ListBox1.RowSource = "a1:f" & CStr(i)], will
NOT refer to QS.Sheets("quotes")
'So, I don't understand what you are trying to do here.
Public Sub InitQuoteSummaryWindow()
Dim RowCount As Long
ScreenUpdating = False
Set QS = Workbooks.Open(QSPath & "QuoteSummary.xls")
RowCount = QS.Sheets("quotes").Range("A1").CurrentRegion.Rows.Count
With frmQuoteSummary.ListBox1
.Font.Name = "Arial"
.Font.Size = 10
.ColumnCount = 6
.ColumnHeads = False
.RowSource = "a1:f" & CStr(RowCount)
.MultiSelect = fmMultiSelectSingle
.ColumnWidths = "72;108;108;108;96;96"
.TextAlign = fmTextAlignLeft
End With
ScreenUpdating = True
QS.Close False
Set QS = Nothing
End Sub
'I don't understand what you are trying to do here, except create and save
an empty file
Private Sub btnSaveNewOE_Click()
Dim FName As String
With Workbooks.Add
'[Range("c9").Value] refers to the activebook. Which is that ?
FName =
Thisworkbook.GetSaveAsFilename(InitialFileName:=(Range("c9").Value) & " " &
Format(Now(), "mmddyy"))
If FName <> False Then
If Right(FName, 1) = "." Then
FName = Left(FName, Len(FName) - 1)
End If
If Right(FName, 3) = "xls" Then
.SaveAs Filename:=FName
Else
.SaveAs Filename:=FName & ".xls"
End If
End If
.Close savechanges:=False
End With
End Sub
Private Function GetMyDocPath1() As String
GetMyDocPath1 = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\My Documents"
End Function
