Macro Sequence of Events

[Code Numpty]

I have remote users a few hundred miles away using a template including
macros. The macro wraps up a file removing formulas, formatting etc. , calls
another macro to log details of the file and finally deletes both modules
from the file. I am being told that it is not working but cannot tell what
someone is doing wrong and need some help with the logic here.

I have a copy of the file concerned and can ascertain that none of the
events in the following macros have happened, with the exception of the
logging process called as Sub logquote().

If someone has the time please would they have a look at the following code
and tell me if it is possible for only the logquote portion to run and
nothing else to happen?

Module3 (Code)-----------------------------------------------
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Sheet1.Range("quote_date") = Sheet1.Range("quote_date").Value
Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Sheet1.Range("content") = Sheet1.Range("content").Value

Call NoDVinputMsg

ActiveSheet.Shapes("Group 31").Delete
Rows("1:1").Delete Shift:=xlUp
ActiveSheet.Shapes("Picture 14").Delete
Range("A:G").Interior.ColorIndex = xlNone

'Desperately trying to speed up delete column E!
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.Range("base_p").Delete Shift:=xlToLeft
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Range("comm_disclines").Delete Shift:=xlUp
Range("boxes").Borders.LineStyle = x1None
Range("delterms_box").ClearContents
Sheets("Instructions").Select
ActiveSheet.Name = "Terms&Conditions"
Range("instructions").Delete
ActiveSheet.Shapes("Picture 1").Delete
Range("A1").Select
Sheets("Quotation").Select
Range("qdata1").Select
Dim vbCom As Object

Call logquote
Application.ScreenUpdating = True

Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With


On Error Resume Next

Set vbCom = ActiveWorkbook.VBProject.VBComponents

vbCom.Remove VBComponent:= _
vbCom.Item("Module3")

vbCom.Remove VBComponent:= _
vbCom.Item("Module4")

On Error GoTo 0

End Sub

Module4 (Code)-----------------------------------------------
Sub NoDVinputMsg()
Dim rng As Range, cel As Range
Set rng = Nothing ' only if rng previously set
On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
If Not rng Is Nothing Then
bDummy = rng.Validation.ShowInput
If Err.Number = 0 Then
' all same type, no need to loop
With rng.Validation
..InputTitle = ""
..InputMessage = ""
End With
Else
On Error GoTo 0
For Each cel In rng
With cel.Validation
..InputTitle = ""
..InputMessage = ""
End With
Next
End If
End If
End Sub
Sub logquote()
'
' logquote Macro
' Macro recorded 15/06/2007 by Sharon
'

'
Dim ThisWorkBook As String
Dim SheetName As String
Dim MyRanges(7) As String
Dim EmptyRow As Integer
Dim a As Integer 'to cyle through ranges

ThisWorkBook = ActiveWorkbook.Name
SheetName = ActiveSheet.Name

MyRanges(1) = "qdata1"
MyRanges(2) = "qdata2"
MyRanges(3) = "qdata3"
MyRanges(4) = "qdata4"
MyRanges(5) = "qdata5"
MyRanges(6) = "qdata6"
MyRanges(7) = "qdata7"

Workbooks.Open Filename:= _
"\\Impactsrv\shared\Templates\Quotes\Quote_Log.xls"
Workbooks("Quote_Log.xls").Activate

With Workbooks("Quote_Log.xls")
.Sheets("Quotes").Activate

With ActiveSheet

'find empty row
EmptyRow = 0
Do
EmptyRow = EmptyRow + 1
Loop Until IsEmpty(.Cells(EmptyRow, 1))

.Cells(EmptyRow, 1) = Date

'fill in other columns from named ranges
For a = 1 To UBound(MyRanges)
.Cells(EmptyRow, a + 1) = _
Workbooks(ThisWorkBook).Sheets(SheetName).Range(MyRanges(a))
Next a

End With

'save and close workbook
.Save
.Close
End With

'activate back to where you started
Workbooks(ThisWorkBook).Activate


End Sub

 

[Joel]

Remove active Sheet and replace with actual sheet names. You are refering to
Named Ranged which may be changing you acitve sheet. Don't use SELECT". The
top half of your code looks good, the bottom part of the code you start to
use SELECT. I suspect you have more than one workbook open and I would
recommend you also put the workbook name into the code or use THISWORKBOOK to
refer to the workbook where the macro is being run.

 

[Code Numpty]

Thanks Joel, I will try to implement most of your suggestions.

Can I ask - If the user has more than one workbook open is it possible for
the macro events to take [place on one workbook but call logquote to run on
another?