User Form

[Guest]

Hi,

I'm trying to call a userform from a macro, using:
UserForm1.show

When I run the macro, I get a Run Time Error 91 Object Variable or With
Block Variable not set error. Because the code returns to the UserForm1.show
statement in the macro rather than going on to the next step.

If I step through it, it proceeds back to the UserForm, and again stops at
the UserForm1.show statement.

I tried Load UserForm1 and had the same problem. How can I get it to
proceed to the next step in the original macro?

Thank you,

Brian

 

[Doug Glancy]

Brian,

In the VBE goto Tools>Options>General and choose "Break in Class Module."
The debugger will now show the line in the userform that's causing the
error.

hth,

Doug

 

[dok112]

Brian,

Do you have any With statements in your macro? You will get the Ru
Time Error 91 sometimes if you have a With statement, but are missin
the End With statement. If thats not the case, copy the code you ar
using to show the userform, and I will look at it and see if I ca
figure out whats causing the problem for you.

 

[Guest]

Hi Dok112 and Doug,

I set the Break in Class and it stops at the same statement, where I
originally show the UserForm1 in the main macro. It looks like it's trying
to repeat that step and not proceeding to the next one.

I don't have any with statements in the userform, so it may have an issue
with the Object Variable part. NOTE: in the macro, the next step after the
Load UserForm1 command is a section named POPULATE:
Not sure if this matters.

Here's the UserForm code I have. Below I copied the section of the main
macro that loads the Userform, with the statements immediately preceding and
following it:

Option Explicit

Private Sub userform_initialize()

Dim AllCells As Range, Cell As Range
Dim myStart As Range
Dim destWB As Workbook
Dim searchltr As String, testltr As String
Dim sourceVal As String
Dim sourceWB As Workbook
Dim sourceRange As Range
Dim x As Long
Dim a As Long

Set sourceWB = Workbooks("CustomerData.xls")
Set destWB = Workbooks("ODonnell Sales Model17.xls")

Set sourceRange = destWB.Sheets("Customer Data").Range("b6")

If sourceRange.Value = "" Then
MsgBox "Please enter a Name"
sourceRange.Select
Exit Sub
End If

If Len(sourceRange.Value) >= 1 Then searchltr = _
UCase(Left(sourceRange.Value, 1))


sourceWB.Activate
Set myStart = Range("D")
x = myStart.End(xlDown).Row - myStart.Row + 1



For a = 2 To x
Set AllCells = Range("d" & a)
Let testltr = UCase(Left(AllCells.Value, 1))
If searchltr = testltr Then
Me.ListBox1.AddItem AllCells.Value
End If
Next a

' Show the UserForm
UserForm1.Show
End Sub




Private Sub CANCEL_Click()
Unload Me

End Sub


Private Sub ListBox1_Click()
Dim AllCells As Range, Cell As Range
Dim myStart As Range
Dim destWB As Workbook
Dim searchltr As String, testltr As String
Dim sourceVal As String
Dim sourceWB As Workbook
Dim sourceRange As Range
Dim a As Long
Dim rng As Range
Dim rng1 As Range
Dim x As Long
Dim keyRange As Range

If Me.ListBox1.ListIndex = -1 Then Exit Sub
Set sourceWB = Workbooks("CustomerData.xls")
Set destWB = Workbooks("ODonnell Sales Model17.xls")

Set sourceRange = destWB.Sheets("Customer Data").Range("B6")


If Len(sourceRange.Value) >= 1 Then searchltr = _
UCase(Left(sourceRange.Value, 1))


sourceWB.Activate
Set myStart = Range("D")
x = myStart.End(xlDown).Row - myStart.Row + 1


For a = 2 To x
Set AllCells = Range("D" & a)
If AllCells.Value = Me.ListBox1.Value Then
Set rng = AllCells
Exit For
End If
Next a

If Not rng Is Nothing Then
Application.Goto rng, True
Else

MsgBox "Not found"
End If

Set keyRange = destWB.Sheets("Sheet3").Range("A7")
ActiveCell.EntireRow.Copy _
Destination:=keyRange


Unload Me

End Sub
______________________________________________________________
Code before and after loading userform:

With sourceWB.Sheets(1).Range("D")
Set rng = .Find(What:=sourceRange.Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If Not rng Is Nothing Then
rng.EntireRow.Copy destrange
Msg = sourceRange.Value & "Loading."
MsgBox (Msg)
GoTo POPULATE
Else

Msg = sourceRange.Value & " Not Found. Would you like to lookup?" ' Define
message.
Style = vbYesNo ' Define buttons.
Title = "Customer Not Found" ' Define title.
Ctxt = 1000 ' Define topic
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then GoTo ender ' User chose No, so end.
End If

Load UserForm1

POPULATE:
Application.ScreenUpdating = False
destWB.Activate
Sheets("Sheet3").Visible = True
Sheets("Calculations").Visible = True
Sheets("Customer Data").Visible = True
Sheets("Calculations").Unprotect
Sheets("Customer Data").Unprotect

 

[Doug Glancy]

Brian,

I should have been clearer. In Debug mode when you're at the Load
statement, use F8 to step through your code. That might, or might not, show
you an error in the Userform itself.

One other thought: instead of loading it in your main module, Show it there
instead. I think there may be some problem with the order of the events as
you have them as I got the same error with just the Load statement in a main
module and just the Show statement in the Initialize event. Showing it in
the main module should not affect your Initialize code, at least I don't
think so.

hth,

Doug

 

[Guest]

Hi Doug,

Thanks for working with me on this. I changed it to Userform1.show in the
main macro, and it goes through the initialize routine, and then shows the
form. When you click on the selection in the userform, it goes through the
Listbox Click routine, but since it didn't end the userform initialize sub,
it goes back to that routine to end the sub. That seems to be where the
error occurs, then it stops at the original Userform.show statement in the
macro.

I inserted an on error resume next in the userform initialize and in the
calling macro, and this seems to work, so I think the problem may be moot,
but it's interesting that it occurred at all.

Thanks for all your help!

Brian