PDA

View Full Version : Solved: Userform Error



phendrena
11-21-2008, 04:18 AM
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,

xld
11-21-2008, 04:27 AM
Worked fine for me.

Why is it so slow?

phendrena
11-21-2008, 04:39 AM
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,

xld
11-21-2008, 04:44 AM
Maybe, I will try it with 97 in a mo.

xld
11-21-2008, 04:49 AM
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
11-21-2008, 05:01 AM
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

xld
11-21-2008, 05:17 AM
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
11-21-2008, 05:23 AM
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

xld
11-21-2008, 05:42 AM
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
11-21-2008, 05:53 AM
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.

xld
11-21-2008, 07:50 AM
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
11-21-2008, 08:05 AM
Excellent, thank you kindly for all your help xld :)

xld
11-21-2008, 08:06 AM
It was interesting playing with Excel 97 again (although that doesn't mean I am in any hurry to repeat it :-))

phendrena
11-21-2008, 08:15 AM
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)

xld
11-21-2008, 08:30 AM
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.

xld
11-21-2008, 08:31 AM
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
11-21-2008, 08:36 AM
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:

xld
11-21-2008, 08:53 AM
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
11-21-2008, 09:38 AM
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 :)