Solved: Userform Error

[phendrena]

Hi There,

I have a userform which is launched from another userform.
When i try to use the second userform (search function) it errors with the following :

Method 'Range' of Object '_Worksheet' Failed
and highlights
Set rSearch = Sheet1.Range("B2", Range("B65536").End(xlUp))

If i run the second userfrom seperately from the worksheet that it references it works fine. Can anyone please provide some assistance?

Forms :
frmDSTCallLog
frmEmailUpdate (References Sheet1)

Thanks,

[Bob Phillips]

Worked fine for me.

Why is it so slow?

[phendrena]

Slow? When you launch the form?
It's setup as a shared workbook, it saves itself like mad to avoid conflic errors (yes, i know shared workbooks = v.bad)....

Works eh?
Hmmmm.... i can launch the first userform fine, and when i clikc on "Land Rover Contingency Emails" it will lauch the second form, but when i try and search it errors.

Is this a fault with excel 97??

Thanks,

[Bob Phillips]

Maybe, I will try it with 97 in a mo.

[Bob Phillips]

It really is time that you upgraded.

Yeah it is a problem with Excel 97. It seems that Excel 97 is a bit more exacting with good object definition.

This seems to work



Set rSearch = Range(Sheet1.Range("B2"), Sheet1.Range("B65536").End(xlUp))

[phendrena]

It really is time that you upgraded.

Yeah it is a problem with Excel 97. It seems that Excel 97 is a bit more exacting with good object definition.

This seems to work



Set rSearch = Range(Sheet1.Range("B2"), Sheet1.Range("B65536").End(xlUp))


Thanks xld, i've changed the code to the above and i works fine, until you enter search criteria that it can find... at which point it errors again :

"Select method of Range class failed"

Highlights :-

Private Sub cmdFind_Click()
Dim strFind, FirstAddress As String
Dim rSearch As Range
Set rSearch = Range(Sheet1.Range("B2"), Sheet1.Range("B65536").End(xlUp))
strFind = Me.txtDealerNo.Value
Dim f As Integer
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then
c.Select
With Me
.txtDealerName.Value = c.Offset(0, -1).Value
.txtContactName.Value = c.Offset(0, 3).Value
.txtEmail.Value = c.Offset(0, 2).Value
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
MsgBox "There are " & f & " instances of " & strFind
End If
Else: MsgBox strFind & " not listed"
End If
End With
End Sub

[Bob Phillips]

LOL! I tested an invalid lookup.

It is because the sheet isn't active, so you either add this line before the c.Select



c.Parent.Activate


or use this



Application.Goto c

[phendrena]

c.Parent.Activate does work nicely, i assume there is no way to avoid having to display the worksheet? I'd prefer it if the end user didn't see sheet when they run the search.....?

Thanks xld

[Bob Phillips]

There is bound to be a way. The original code did the selecting, so I have to ask you, what are you trying to do, why did you select the found cell?

[phendrena]

There is bound to be a way. The original code did the selecting, so I have to ask you, what are you trying to do, why did you select the found cell?Good point, we can do away with the visual selecting as it's not really needed. All the user needs to see and use is the form.

One last thing, i can't seem to change the worksheet reference from Sheet1 to anything else without the code thowing an error.

I've added ..... set wks = worksheets("NAME") and then changed the references to Sheet1 in the code to wks (also tried NAME without the set wks) and it doesn't like it. Do i need to place it elsewhere or define it differently?

Private Sub cmdFind_Click()
Dim strFind, FirstAddress As String
Dim rSearch As Range
Set wks = Worksheets("NAME")
Set rSearch = Range(wks.Range("B2"), wks.Range("B65536").End(xlUp))
strFind = Me.txtDealerNo.Value
Dim f As Integer
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then
c.Parent.Activate
c.Select
With Me
.txtDealerName.Value = c.Offset(0, -1).Value
.txtContactName.Value = c.Offset(0, 2).Value
.txtEmail.Value = c.Offset(0, 1).Value
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
MsgBox "There are " & f & " instances of " & strFind
End If
Else: MsgBox strFind & " not listed"
End If
End With
End Sub

Thanks for all your help so far xld.

[Bob Phillips]

This works fine for me



Private Sub cmdFind_Click()
Dim strFind, FirstAddress As String
Dim rSearch As Range
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")
Set rSearch = Range(wks.Range("B2"), wks.Range("B65536").End(xlUp))
strFind = Me.txtDealerNo.Value
Dim f As Integer
With rSearch

Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then

With Me

.txtDealerName.Value = c.Offset(0, -1).Value
.txtContactName.Value = c.Offset(0, 3).Value
.txtEmail.Value = c.Offset(0, 2).Value
f = 0
End With
FirstAddress = c.Address
Do

f = f + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress

If f > 1 Then

MsgBox "There are " & f & " instances of " & strFind
End If
Else

MsgBox strFind & " not listed"
End If
End With
End Sub

[phendrena]

Excellent, thank you kindly for all your help xld :)

[Bob Phillips]

It was interesting playing with Excel 97 again (although that doesn't mean I am in any hurry to repeat it :-))

[phendrena]

It was interesting playing with Excel 97 again (although that doesn't mean I am in any hurry to repeat it :-))ALL my queries will be excel 97 as it's what i'm forced to use at work :(

(I've got Office 2000 & XP at home)

[Bob Phillips]

Yeah, I remember having this discussion with you before.

One thing is does point out that it is important to use good object qualification, you never know when all of these nasty shortcuts will catch you out.

[Bob Phillips]

BTW, changing from the module codename to an worksheet object was not a good idea IMO. YOu have control over the codename, user may rename the worksheet.

[phendrena]

Gotta say i always tend to rename my worksheets (the tabs) so i know where i am upto. It never occured to me that some twit might try and change them.... still on the end user spreadsheet I tend to hide all row/column headers and sheet tabs... it helps that 99% of the end users in the office to far to scared to actually mess with anything that they don't understand (if it's not a normal looking spreadsheet they run for the hills) :stars:

[Bob Phillips]

Maybe best to rename them all in the workbook open evennt then liks o



Private Sub Workbook_Open()

Sheet1.Name = "Menu"
Sheet2.Name = "Lookups"
'etc.
End Sub


Just ensures that you start okay.

[phendrena]

Maybe best to rename them all in the workbook open evennt then liks o



Private Sub Workbook_Open()

Sheet1.Name = "Menu"
Sheet2.Name = "Lookups"
'etc.
End Sub


Just ensures that you start okay.

Great idea, thanks again xld :)