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:

[VBA]
Public Sub DifferenceRC()
[/VBA]