variable not transferring?

[davegb]

With a lot of help here, I've written some code to compare 2
spreadsheets. But when I couple the code to run a userform to get the
filenames to compare with the code to align the data in corresponding
rows, the second macro doesn't recognize the workbook names that have
been saved in the first macro.

Here's the code with the userform:

Option Explicit
Public wsNew As Worksheet
Public wsOrig As Worksheet
Public wbNew As Workbook
Public wbOrig As Workbook


Private Sub CancelButton_Click()
Unload UserForm2
End Sub

Private Sub OkButton_Click()
'Workbooks(ComboBox1.Text).Select
Set wbNew = Workbooks(ComboBox1.Text)
Set wbOrig = Workbooks(ComboBox2.Text)
'wbNew.Activate
UserForm2.Hide
Call EvenOutRows

End Sub

Private Sub UserForm_Initialize()
Dim wbk As Workbook
For Each wbk In Workbooks
ComboBox1.AddItem wbk.Name
ComboBox2.AddItem wbk.Name
Next wbk

End Sub

And the other code:

Sub EvenOutRows()

Dim wsNew As Worksheet
Dim wsOrig As Worksheet
Dim lCurRow As Long
Dim lrowNew As Long
Dim lrowOrig As Long
UserForm2.Show

Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR
WITHBLOCK VARIABLE NOT SET

Set wsNew = wbNew.Worksheets("sheet1")

lCurRow = 2
Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _
Not IsEmpty(wsOrig.Cells(lCurRow, 1))
If wsNew.Cells(lCurRow, 1) > wsOrig.Cells(lCurRow, 1) Then
wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1
ElseIf wsOrig.Cells(lCurRow, 1) > wsNew.Cells(lCurRow, 1) Then
wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1
ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then
lCurRow = lCurRow + 1
End If
Loop
Application.Run "Compare.xla!Compare"
End Sub

The variables have been declared publicly in both modules. Any
suggestions?

 

[Tom Ogilvy]

Option Explicit
Public wsNew As Worksheet
Public wsOrig As Worksheet
Public wbNew As Workbook
Public wbOrig As Workbook

needs to be placed at the top of a general module.

Public variables in a general module have project wide scope. Public
variables in a userform have module level scope by default although they can
also be treated as properties of the class.

 

[davegb]

Option Explicit
Public wsNew As Worksheet
Public wsOrig As Worksheet
Public wbNew As Workbook
Public wbOrig As Workbook

needs to be placed at the top of a general module.

Public variables in a general module have project wide scope. Public
variables in a userform have module level scope by default although they can
also be treated as properties of the class.

Tom, thanks for your help.

I had declared the variables publicly in both the userform module and
the general module. When I read your reply, I remarked them out in the
userform module. Now I'm getting a Subscript out of range error on that
same code. Any suggestions?

 

[Dave Peterson]

Are you getting the error on one of these lines?

Set wbNew = Workbooks(ComboBox1.Text)
Set wbOrig = Workbooks(ComboBox2.Text)

If yes, then the current value in that combobox (that's causing the error)
doesn't refer back to a workbook that's open.

Could it be that the user hasn't made a choice and that .text value is still
""???

 

[davegb]

Are you getting the error on one of these lines?

Set wbNew = Workbooks(ComboBox1.Text)
Set wbOrig = Workbooks(ComboBox2.Text)

If yes, then the current value in that combobox (that's causing the error)
doesn't refer back to a workbook that's open.

Could it be that the user hasn't made a choice and that .text value is still
""???

Thanks for your reply, Dave.

I found the problem. The user supplying the spreadsheets to be compared
has been renaming the first sheet instead of leaving it "Sheet1"! Doh!

Thanks again to all.

 

[Dave Peterson]

Glad you found the problem.

(I didn't notice your not in your code. Sorry.)

 

[Dave Peterson]

(I didn't notice your notE in your code. Sorry.)