variable not transferring?

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.

--
Regards,
Tom Ogilvy

"davegb" wrote:

> 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 wrote:
> 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.
>
> --
> Regards,
> Tom Ogilvy

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?

>
> "davegb" wrote:
>
> > 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?
> >
> >

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 wrote:
>
> Tom Ogilvy wrote:
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
>
> 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?
>
> >
> > "davegb" wrote:
> >
> > > 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?
> > >
> > >

--

Dave Peterson

Dave Peterson wrote:
> 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.


> davegb wrote:
> >
> > Tom Ogilvy wrote:
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> >
> > 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?
> >
> > >
> > > "davegb" wrote:
> > >
> > > > 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?
> > > >
> > > >
>
> --
>
> Dave Peterson

Glad you found the problem.

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

davegb wrote:
>
> Dave Peterson wrote:
> > 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.
>
> > davegb wrote:
> > >
> > > Tom Ogilvy wrote:
> > > > 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.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > >
> > > 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?
> > >
> > > >
> > > > "davegb" wrote:
> > > >
> > > > > 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?
> > > > >
> > > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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

Dave Peterson wrote:
>
> Glad you found the problem.
>
> (I didn't notice your not in your code. Sorry.)
>
> davegb wrote:
> >
> > Dave Peterson wrote:
> > > 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.
> >
> > > davegb wrote:
> > > >
> > > > Tom Ogilvy wrote:
> > > > > 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.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > >
> > > > 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?
> > > >
> > > > >
> > > > > "davegb" wrote:
> > > > >
> > > > > > 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?
> > > > > >
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson