Help, my macro always opens up spreadsheet it was created in

C

CindyMac

Hi,

I received a file with a macro that creates a interactive form for changing
data. My problem is that everytime I want to use this macro in a new
spreadsheet it alway opens up the spreadsheet where the macro was created.
How can continue to use this macro without the other file opening?

Thank you, Cindy
 
D

Dave Peterson

It sounds like you're clicking on something to start that macro.

But the macro that's assigned to that thing is still pointing to that original
workbook.

Maybe just changing that assignment to point at the macro in the current (?)
workbook would be sufficient.
 
C

CindyMac

Hi Don,

Well I didn't know if I could view the code, but I found it and it doesn't
seem to be obvious to me if it's conned to a file.


Private Sub Cancel_Click()
End
End Sub

Private Sub Update_Click()
If EACR = "" Then
MsgBox "EACR number is missing, please add.", vbInformation,
"Missing Information"
ElseIf OldSeries = "" Then
MsgBox "Old Series is missing, please add.", vbInformation, "Missing
Information"
ElseIf NewSeries = "" Then
MsgBox "New Series is missing, please add.", vbInformation, "Missing
Information"
Else
Application.ScreenUpdating = False
Range("A2").Select
Do Until Selection.Offset(0, 0) = ""
If Selection.Value = EACR Then
If Selection.Offset(0, 1) = OldSeries Then
Selection.Offset(0, 1) = NewSeries
End If
End If
Selection.Offset(1, 0).Select
Loop
Range("A2").Activate
Application.ScreenUpdating = True
End
End If
End Sub

Private Sub UserForm_Initialize()

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in A1:A105
Set AllCells = Range("A2:A10000")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
Me.EACR.AddItem Item
Next Item

End Sub
 
Top