Consulting

Results 1 to 19 of 19

Thread: Solved: Userform Error

  1. #1
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location

    Solved: Userform Error

    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,
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Worked fine for me.

    Why is it so slow?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    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,
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Maybe, I will try it with 97 in a mo.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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

    [vba]

    Set rSearch = Range(Sheet1.Range("B2"), Sheet1.Range("B65536").End(xlUp))
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    Quote Originally Posted by xld
    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

    [vba]

    Set rSearch = Range(Sheet1.Range("B2"), Sheet1.Range("B65536").End(xlUp))
    [/vba]
    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 :-

    [vba]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[/vba]
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    LOL! I tested an invalid lookup.

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

    [vba]

    c.Parent.Activate
    [/vba]

    or use this

    [vba]

    Application.Goto c
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    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
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    Quote Originally Posted by xld
    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?

    [vba]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[/vba]

    Thanks for all your help so far xld.
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    This works fine for me

    [vba]

    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
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    Excellent, thank you kindly for all your help xld
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It was interesting playing with Excel 97 again (although that doesn't mean I am in any hurry to repeat it :-))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    Quote Originally Posted by xld
    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)
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  17. #17
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    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)
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

  18. #18
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Maybe best to rename them all in the workbook open evennt then liks o

    [vba]

    Private Sub Workbook_Open()

    Sheet1.Name = "Menu"
    Sheet2.Name = "Lookups"
    'etc.
    End Sub
    [/vba]

    Just ensures that you start okay.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  19. #19
    VBAX Tutor phendrena's Avatar
    Joined
    Oct 2008
    Location
    Huddersfield, UK
    Posts
    285
    Location
    Quote Originally Posted by xld
    Maybe best to rename them all in the workbook open evennt then liks o

    [vba]

    Private Sub Workbook_Open()

    Sheet1.Name = "Menu"
    Sheet2.Name = "Lookups"
    'etc.
    End Sub
    [/vba]

    Just ensures that you start okay.
    Great idea, thanks again xld
    Somewhere in the dark and nasty regions where nobody goes, stands an ancient castle.
    Deep within this dank and uninviting place lives Berk, overworked servant of The Thing Upstairs.
    But thats nothing compared to the horrors that lurk beneath The Trap Door.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •