Another minor Bug :( passing String variable

[itipu]

I have a code in a UserForm which goes like this:



Public Sub CommandButton1_Click()
which = ListBox2.Text
Sheets(ListBox1.Text).Activate
Unload Me
End Sub
Sub UserForm_Initialize()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Computer") > 0 And sh.Visible = True Then
ListBox1.AddItem sh.Name
ElseIf InStr(1, sh.Name, "RHN") > 0 And sh.Visible = True Then
ListBox2.AddItem sh.Name
End If
Next
End Sub
Public Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Select Case CloseMode
Case 0: frmExtract.HowClosed = "Forced"
Case 1: frmExtract.HowClosed = "Selected"
End Select
End Sub


which is declared in a module as Global which As String

I try to pass which to the code below located in a different form, but it doesn't work where I marked it with red ;( As always appreciate your help very much!!!!!!!!!!!!!!


Public Sub DifferenceRC(which)
Dim rngA As Range
Dim rngB As Range
Dim rngOut As Range
Dim rngCell As Range
Dim rngFind As Range
Dim Sheet As String
Dim sh As Worksheet

UserForm2.Show

Sheet = "Missing Billing Item"
On Error Resume Next
Set rngA = ActiveSheet.UsedRange
Set rngB = Sheets(which).UsedRange.Columns(1)
Set sh = Worksheets(Sheet)
On Error GoTo 0
If sh Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
Set sh = ActiveSheet
sh.Name = Sheet
End If
sh.Cells.ClearContents

For Each rngCell In rngA.Columns(1).Cells
Set rngFind = rngB.Find(rngCell.Value, , LookIn:=xlValues, LookAt:=xlWhole)
If rngFind Is Nothing Then
rngCell.EntireRow.Copy sh.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next
Unload frmExtract
End Sub

[macleanb]

Hi

I suspect you may be suffering one of the most frustrating VBA bugs I have ever come accross - it had me running in a circle for DAYS!

THere is an issue with UserForms & Global variables, that if you have the FormEditor open when you are running the macro, there is a nasty tendency for all globals to be reset to empty when the form exits.

Try this - just close al the form windows in the VBE env, and try running again!


ttfn benm

[itipu]

Well I am doing it all though Excel, and yes, my variable arrives empty... but closing things doesn't solve anything... Maybe I am missing something :(((((((

Mike

[macleanb]

You dont have any break points in your form code that would cause the VBE to open the form code?

[itipu]

Nope nothing

[Ivan F Moala]

try



Public Sub CommandButton7_Click()
DifferenceRC which
End Sub


No brackets

[macleanb]

ARgghhh! Well spotted Ivan, the OP is declaring a locally scoped variable "which" as well as the global "which", so he could alternatively just drop the "which" from the sub declaration:


Public Sub DifferenceRC()