run-time error 438 object does not support this property or method

N

nuttycow13

New Member
Joined
Feb 6, 2012
Messages
47
I have been getting this error: "run-time error 438 object does not support this property or method " when trying to get a click button to search a spread sheet find a value from the entered text box and then read the cells and input them into text boxes in another userform.

this is the code i have:

Private Sub Printer_Find_Click()

If Sitebox.Value = "UKCH" Then
Sheet2.Activate
ElseIf Sitebox.Value = "SDHQ" Then
Sheet1.Activate
ElseIf Sitebox.Value = "NLEI" Then
Sheet13.Activate
ElseIf Sitebox.Value = "USHW" Then
Sheet10.Activate
ElseIf Sitebox.Value = "SGSG" Then
Sheet11.Activate
End If

Range("B1").Avtive
Do While ActiveCell.Value <> ""
If ActiveCell.Value = Find_Printer.Text Then
Found_Printer_Name.Value = Cells(0, 2).Value
Found_Vaild.Value = Cells(0, 18).Value
Found_DNS.Value = Cells(0, 14).Value
Found_IP.Value = Cells(0, 12).Value
Found_Patch.Value = Cells(0, 7).Value
Found_Fax.Value = Cells(0, 10).Value
Found_Serial.Value = Cells(0, 6).Value
Found_Model.Value = Cells(0, 5).Value
Found_Make.Value = Cells(0, 4).Value
Found_Wing.Value = Cells(0, 8).Value
Found_Mac.Value = Cells(0, 11).Value
Found_Host.Value = Cells(0, 12).Value
Found_GIS.Value = Cells(0, 17).Value
Found_Comments.Value = Cells(0, 19).Value
Found_Type.Value = Cells(0, 3).Value
Found_Location.Value = Cells(0, 9).Value
Found_Site.Value = Sitebox.Value
Found_Number.Value = Numberbox.Value


End If

ActiveCell.Offset(1, 0).Activate
Loop

Unload Me

Found_Printer.Show vbModal

End Sub

it would be great if anyone could help me. thanks in advance
 
Sort by date Sort by votes
i have sorted the names and got that part done ok but, as i said it still wont search column B for the value in text box Find_Printer, it just seems to highlight the whole column.
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Which forms do we need to be looking at?

Any particular data that should be entered?

I think I can see problems being caused by the use of ActiveSheet throughout the code.

I know you use Activate but it would be better to directly reference the worksheets.

For example:
Code: 
Private Sub Find_Click()
Dim ws As Worksheet
Dim rng As Range
    
 
      ' possibly use Set ws = Workshsets(Sitebox.Value)?
 
    Select Case SiteBox.Value
      Case "UKCH"
        Set ws = Sheet2
     Case "SDHQ"
        Set ws = Sheet1
        '''''....
    
     Case "SGSG" 
        Set ws = Sheet11.Activate
    End Select
 
 
    Set rng = ws.Range("B1")
 
    Do While rng.Value <> ""
        If rng.Value = Find_Printer.Text Then
            Found_Printer.Found_Printer_Name.Value = rng.Offset(0, 2).Value
            Found_Printer.Found_Vaild.Value = rng.Offset(0, 18).Value
            Found_Printer.Found_DNS.Value = rng.Offset(0, 14).Value
            Found_Printer.Found_IP.Value = rng.Offset(0, 12).Value
            Found_Printer.Found_Patch.Value = rng.Offset(0, 7).Value
            Found_Printer.Found_Fax.Value = rng.Offset(0, 10).Value
            Found_Printer.Found_Serial.Value = rng.Offset(0, 6).Value
            Found_Printer.Found_Model.Value = rng.Offset(0, 5).Value
            Found_Printer.Found_Make.Value = rng.Offset(0, 4).Value
            Found_Printer.Found_Wing.Value = rng.Offset(0, 8).Value
            Found_Printer.Found_Mac.Value = rng.Offset(0, 11).Value
            Found_Printer.Found_Host.Value = rng.Offset(0, 12).Value
            Found_Printer.Found_GIS.Value = rng.Offset(0, 17).Value
            Found_Printer.Found_Comments.Value = rng.Offset(0, 19).Value
            Found_Printer.Found_Type.Value = rng.Offset(0, 3).Value
            Found_Printer.Found_Location.Value = rng.Offset(0, 9).Value
            Found_Printer.Found_Site.Value = Sitebox.Value
            Found_Printer.Found_Number.Value = Numberbox.Value
        End If
 
        Set rng = rng.Offset(1)
    Loop
 
End Sub
 
Upvote 0
Thanks for the change of code, its now showing another error message:

Runt-Time Error 91: Object variable or with block variable not set.

Private Sub Printer_Find_Click()
Dim ws As Worksheet
Dim rng As Range


' possibly use Set ws = Workshsets(Sitebox.Value)?

Select Case Sitebox.Value
Case "UKCH"
Set ws = Sheet2
Case "SDHQ"
Set ws = Sheet1
Case "NLEI"
Set ws = Sheet13
Case "USHW"
Set ws = Sheet10
Case "SGSG"
Set ws = Sheet11
End Select


Set rng = ws.Range("B1")

Do While rng.Value <> ""
If rng.Value = Find_Printer.Text Then
Found_Printer.Printer_Name.Value = rng.Offset(0, 2).Value
Found_Printer.Vaild.Value = rng.Offset(0, 18).Value
Found_Printer.DNS.Value = rng.Offset(0, 14).Value
Found_Printer.IP.Value = rng.Offset(0, 12).Value
Found_Printer.Found_Patch.Value = rng.Offset(0, 7).Value
Found_Printer.Found_Fax.Value = rng.Offset(0, 10).Value
Found_Printer.Found_Serial.Value = rng.Offset(0, 6).Value
Found_Printer.Found_Model.Value = rng.Offset(0, 5).Value
Found_Printer.Found_Make.Value = rng.Offset(0, 4).Value
Found_Printer.Found_Wing.Value = rng.Offset(0, 8).Value
Found_Printer.Found_Mac.Value = rng.Offset(0, 11).Value
Found_Printer.Host.Value = rng.Offset(0, 12).Value
Found_Printer.GIS.Value = rng.Offset(0, 17).Value
Found_Printer.Comments.Value = rng.Offset(0, 19).Value
Found_Printer.Found_Type.Value = rng.Offset(0, 3).Value
Found_Printer.Found_Location.Value = rng.Offset(0, 9).Value
Found_Printer.Found_Site.Value = Sitebox.Value
Found_Printer.Found_Number.Value = Numberbox.Value
End If

Set rng = rng.Offset(1)
Loop

Found_Printer.Show vbModal

End Sub
 
Upvote 0
That means that ws hasn't been set which could be because the value in Sitebox doesn't match any of the cases in the Select Case.

Check that value and check the names in the code - I may have made a small typo.
 
Upvote 0
I have sorted it but now i have another issues, i get the error message:

Run-time error 13: type mismatch

I am using the code:
Code: 
Private Sub Printer_Find_Click()
Dim ws As Worksheet
Dim rng As Range
 
  On Error Resume Next
Set ws = Sheets(Sitebox.Value)
On Error GoTo 0
 
If ws Is Nothing Then MsgBox "Invalid sheet input! Exiting Sub": Exit Sub
 
    Set rng = ws.Range("B1:B9999")
 
    Do While rng.Value <> ""
        If rng.Value = Find_Printer.Text Then
            Found_Printer.Printer_Name.Value = rng.Offset(0, 0).Value
            Found_Printer.Vaild.Value = rng.Offset(0, 16).Value
            Found_Printer.DNS.Value = rng.Offset(0, 12).Value
            Found_Printer.IP.Value = rng.Offset(0, 10).Value
            Found_Printer.Found_Patch.Value = rng.Offset(0, 5).Value
            Found_Printer.Found_Fax.Value = rng.Offset(0, 8).Value
            Found_Printer.Found_Serial.Value = rng.Offset(0, 4).Value
            Found_Printer.Found_Model.Value = rng.Offset(0, 3).Value
            Found_Printer.Found_Make.Value = rng.Offset(0, 2).Value
            Found_Printer.Found_Wing.Value = rng.Offset(0, 6).Value
            Found_Printer.Found_Mac.Value = rng.Offset(0, 9).Value
            Found_Printer.Host.Value = rng.Offset(0, 11).Value
            Found_Printer.GIS.Value = rng.Offset(0, 15).Value
            Found_Printer.Comments.Value = rng.Offset(0, 17).Value
            Found_Printer.Found_Type.Value = rng.Offset(0, 1).Value
            Found_Printer.Found_Location.Value = rng.Offset(0, 7).Value
            Found_Printer.Found_Site.Value = Sitebox.Value
            Found_Printer.Found_Number.Value = Numberbox.Value
        End If
 
        Set rng = rng.Offset(1)
    Loop
    
 Found_Printer.Show vbModal
 
End Sub
 
Upvote 0
Sorry just noticed i didnt show the line in code that error occurs its:

Rich (BB code): 
Private Sub Printer_Find_Click()
Dim ws As Worksheet
Dim rng As Range
 
  On Error Resume Next
Set ws = Sheets(Sitebox.Value)
On Error GoTo 0
 
If ws Is Nothing Then MsgBox "Invalid sheet input! Exiting Sub": Exit Sub
 
    Set rng = ws.Range("B1:B9999")
 
    Do While rng.Value <> ""
        If rng.Value = Find_Printer.Text Then
            Found_Printer.Printer_Name.Value = rng.Offset(0, 0).Value
            Found_Printer.Vaild.Value = rng.Offset(0, 16).Value
            Found_Printer.DNS.Value = rng.Offset(0, 12).Value
            Found_Printer.IP.Value = rng.Offset(0, 10).Value
            Found_Printer.Found_Patch.Value = rng.Offset(0, 5).Value
            Found_Printer.Found_Fax.Value = rng.Offset(0, 8).Value
            Found_Printer.Found_Serial.Value = rng.Offset(0, 4).Value
            Found_Printer.Found_Model.Value = rng.Offset(0, 3).Value
            Found_Printer.Found_Make.Value = rng.Offset(0, 2).Value
            Found_Printer.Found_Wing.Value = rng.Offset(0, 6).Value
            Found_Printer.Found_Mac.Value = rng.Offset(0, 9).Value
            Found_Printer.Host.Value = rng.Offset(0, 11).Value
            Found_Printer.GIS.Value = rng.Offset(0, 15).Value
            Found_Printer.Comments.Value = rng.Offset(0, 17).Value
            Found_Printer.Found_Type.Value = rng.Offset(0, 1).Value
            Found_Printer.Found_Location.Value = rng.Offset(0, 7).Value
            Found_Printer.Found_Site.Value = Sitebox.Value
            Found_Printer.Found_Number.Value = Numberbox.Value
        End If
 
        Set rng = rng.Offset(1)
    Loop
    
 Found_Printer.Show vbModal
 
End Sub
 
Upvote 0
Why have you set rng to B1:B9999?

Also, why have you used On Error Resume Next?

I thought the combobox SiteValue only listed the site names and they corresponded to the sheet names.

If the problem is when nothing has been selected from the list in the combobox you can check like this:
Code: 
' check site selected
 
If Sitebox.ListIndex = -1 Then
   ' no site selected
    MsgBox "Please select a site from the list. ", vbCritical
Else
   ' site selected, continue with rest of code
End If
This goes right at the start of the code just under the variable declarations.

PS Have you updated the file you uploaded with the changes you've made?
 
Upvote 0
well i had "B1" before and it would only search one cell and i want it to search everything in column B so thought it would be best to put B1:B9999 but this did not work how else can this be done ??
 
Upvote 0
Well you can't use the whole range you want to search.

The only reason I can see the code only looking at one cell would be if the cell below it was blank.

The loop would be skipped entirely if the first cell was blank.

What is this form actually doing anyway?

Couldn't you just have a combobox/listbox that displays the printers for the selected site?

Then the user selects the one they are interested in and continues.
 
Upvote 0
There are some blank cells and i need it to be able to check the row and if blank skip over and keep checking until it finds say 2 blank cells in the column and then stop.

this form has printers added to and then search against, there are to many printer to put in a drop down box. plus once the printer has been found i need all the other cells displayed into text boxes it works fine until the blank cell.

is this able to be done. ??

here is a link to what i have so far

http://www.box.com/s/tg7y1he5jsh2qalyoyd1
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Run Time error 438 - Object doesn't support this property or method
Replies
2
Views
2K
Gliffix101
G
R
  • Question
Comparing data on two workbooks: Run-time error 438 - Object does not support this propery or method
Replies
2
Views
575
raivyne
R
Chookz
  • Question
Please help with VBA code - Run-time error '438': Object doesn't support this property or method
Replies
6
Views
6K
ukmikeb
U
J
  • Question
run time error 438 "Object Does not Support this property or method
Replies
4
Views
678
jamilm
J
T
  • Question
Application.Worksheetfunction.Mid - Run-time error '438: Object doesn't support this property or method
Replies
9
Views
9K
James Snyder
J

Forum statistics

Threads
1,214,976
Messages
6,122,543
Members
449,089
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top