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

[nuttycow13]

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

 

[Norie]

So people are entering the printer number themselves?

Why not do that with the code?

Then you could add new printers and the end of the list.

As for the code, how is it not working?

Can you give me an example so I can test it?

 

[nuttycow13]

ok when they add the printer the code gives them the printer number.

when they are searching for the printer they put the number in.

And the code is finding the cell but not sending the details to the text box.

its the same details as in:

http://www.box.com/s/tg7y1he5jsh2qalyoyd1

and the code to search and amend is:

Private Sub Amend_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")
 
    Do While rng.Value <> ""
        If rng.Value = Find_Printer.Text Then
            rng.Offset(0, 0).Value = Find_Printer.Value
            rng.Offset(0, 16).Value = Vaild.Value
            rng.Offset(0, 12).Value = DNS.Value
            rng.Offset(0, 10).Value = IP.Value
            rng.Offset(0, 5).Value = Patch.Value
            rng.Offset(0, 8).Value = Fax.Value
            rng.Offset(0, 4).Value = Serial.Value
            rng.Offset(0, 3).Value = Model.Value
            rng.Offset(0, 2).Value = Make.Value
            rng.Offset(0, 6).Value = Wing.Value
            rng.Offset(0, 9).Value = Mac.Value
            rng.Offset(0, 11).Value = Host.Value
            rng.Offset(0, 15).Value = GIS.Value
            rng.Offset(0, 17).Value = Comments.Value
            rng.Offset(0, 1).Value = Printer_Type_box.Value
            rng.Offset(0, 7).Value = Locationbox.Value
            Sitebox.Value = Sitebox.Value
            Numberbox.Value = Numberbox.Value
        End If
 
        Set rng = rng.Offset(1)
    Loop
    Sheet3.Active
    Unload Me
End Sub
Private Sub Display_Printer_Name_Click()

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

' inputs the information from the 4 input boxes and generates the Printer Name when clicked
Find_Printer = Sitebox + "-" & Locationbox & "-" & Printer_Type_box & Numberbox
End Sub
Private Sub Enter_Number_Click()
' Sets Location as range
Dim Location As Range
'Actives worksheet as lookup USSD
Dim ws As Worksheet
Set ws = Worksheets("LookUp")
'Formats the textbox to 4 numbers with leading 0
Numberbox.Text = Format(Numberbox, "0000")
' Makes sheet5 (LookUp Sheet) Active
Sheet5.Activate

' This if statement checks the input from the site combobox and depending on the input selected
' It will then find the correct Location lookup From the lookup worksheet
If Sitebox.Value = "UKCH" Then
For Each Location In ws.Range("locationUKCH")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location
ElseIf Sitebox.Value = "SDHQ" Then
For Each Location In ws.Range("locationSDHQ")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location
ElseIf Sitebox.Value = "NLEI" Then
For Each Location In ws.Range("locationNLEI")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location
ElseIf Sitebox.Value = "USHW" Then
For Each Location In ws.Range("locationUSHW")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location
ElseIf Sitebox.Value = "SGSG" Then
For Each Location In ws.Range("locationSGSG")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location
End If
End Sub

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
 

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
 
For i = 2 To LastRow
     Set rng = ws.Range("B" & i)
 
     If rng.Value = Numberbox.Value Then
         ' code to populate textboxes on userform
            Find_Printer.Value = rng.Offset(0, 0).Value
            Vaild.Value = rng.Offset(0, 16).Value
            DNS.Value = rng.Offset(0, 12).Value
            IP.Value = rng.Offset(0, 10).Value
            Patch.Value = rng.Offset(0, 5).Value
            Fax.Value = rng.Offset(0, 8).Value
            Serial.Value = rng.Offset(0, 4).Value
            Model.Value = rng.Offset(0, 3).Value
            Make.Value = rng.Offset(0, 2).Value
            Wing.Value = rng.Offset(0, 6).Value
            Mac.Value = rng.Offset(0, 9).Value
            Host.Value = rng.Offset(0, 11).Value
            GIS.Value = rng.Offset(0, 15).Value
            Comments.Value = rng.Offset(0, 17).Value
            Printer_Type_box.Value = rng.Offset(0, 1).Value
            Locationbox.Value = rng.Offset(0, 7).Value
            
     End If
Next i

'    Set rng = ws.Range("B1")
 
    'Do While rng.Value <> ""
      '  If rng.Value = Find_Printer.Value Then
     
     ' End If
 
    '    Set rng = rng.Offset(1)
   ' Loop
    
End Sub

Private Sub UserForm_Initialize()
Dim Site As Range
Dim Location As Range
Dim Printer_Type As Range
Dim ws As Worksheet
Set ws = Worksheets("LookUp")

For Each Site In ws.Range("Site")
With Me.Sitebox
.AddItem Site.Value
End With
Next Site

For Each Printer_Type In ws.Range("Type")
With Me.Printer_Type_box
.AddItem Printer_Type.Value
End With
Next Printer_Type

End Sub
Private Sub ClearForm_Click()
'// Cancels form
Unload Me
'// and reopens form
Amend_Printer.Show vbModal
End Sub
Private Sub Cancel_From_Click()
'// makes the home screen active
Sheet3.Activate
'// cancels and closes form
Unload Me
End Sub

 

[Norie]

Any chance of an example?

ie what I should enter/select on which form to replicate the problem

 

[Norie]

I'm totally confused.

You've not updated the uploaded file, it still has this for the code for the Find Printer button.

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

 

[nuttycow13]

Sorry the new link must not have copied across correctly.

http://www.box.com/s/tg7y1he5jsh2qalyoyd1

this should be correct.

ok when i am on the home tab and click on amend i would type in a printer details I.E
SITE = SDHQ
NUMBER 38 AND CLICK ENTER NUMBER
LOCATION 13
TYPE CP AND THEN hit display printer name and then find printer,

if this worked all the other boxes would fill with the printer information but at the moment nothing happens.

 

[Norie]

The code I posted wasn't for the find button on the amend form.

Anyway, for that button try this.

Private Sub Printer_Find_Click()
Dim ws As Worksheet
Dim rng As Range
Dim I As Long
  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
 
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
 
For I = 2 To LastRow
     Set rng = ws.Range("A" & I)
 
     ' need to convert text in Numberbox to a number to match the numeric value in rng
     If rng.Value = Val(Numberbox.Value) Then
         ' code to populate textboxes on userform
            Find_Printer.Value = rng.Offset(0, 0).Value
            Vaild.Value = rng.Offset(0, 16).Value
            DNS.Value = rng.Offset(0, 12).Value
            IP.Value = rng.Offset(0, 10).Value
            Patch.Value = rng.Offset(0, 5).Value
            Fax.Value = rng.Offset(0, 8).Value
            Serial.Value = rng.Offset(0, 4).Value
            Model.Value = rng.Offset(0, 3).Value
            Make.Value = rng.Offset(0, 2).Value
            Wing.Value = rng.Offset(0, 6).Value
            Mac.Value = rng.Offset(0, 9).Value
            Host.Value = rng.Offset(0, 11).Value
            GIS.Value = rng.Offset(0, 15).Value
            Comments.Value = rng.Offset(0, 17).Value
            Printer_Type_box.Value = rng.Offset(0, 1).Value
            Locationbox.Value = rng.Offset(0, 7).Value
            
     End If
Next I

    
End Sub

By the way, why are you using the Enter Number to populate the Location list?

Wouldn't it make more sense to use the change event of the Site combobox?

With that the user wouldn't need to press any buttons, the Location list would be populated as soon as a site was selected.

 

[nuttycow13]

Hi thanks that works a treat just amended the cells to the textbox and it worked really thank you its been bugging me all the time.

i used the enter number as i could not get the the change event of the Site combobox to change the Location list i would prefer this to work like that but didn't work and could not figure it out. i am going to try and get it to work but needed to get it up and running and once it is figure out now that part.

so that's the stage i am at home. if you could help with explain how to get it working that would be great but if you don't have the free time its doesn't matter you have helped me out a lot already. thank you.

 

[Norie]

How to get what working?

If you mean populating the combobox on the Amend form try this.

Private Sub Sitebox_Change()
' populates location combobox based on site selected
Dim ws As Worksheet
Dim Location As Range
 
    Set ws = Worksheets("LookUp")

    'Formats the textbox to 4 numbers with leading 0
    Numberbox.Text = Format(Numberbox, "0000")
 
    Select Case Sitebox.Value
        Case "UKCH"
            Set Location = ws.Range("locationUKCH")
        Case "SDHQ"
            Set Location = ws.Range("locationSDHQ")
        Case "NLEI"
            Set Location = ws.Range("locationNLEI")
        Case "USHW"
            Set Location = ws.Range("locationUSHW")
        Case "SGSG"
            Set Location = ws.Range("locationSGSG")
    End Select
 
    Set Location = Location.Resize(Application.CountA(Location))
 
    Locationbox.List = Location.Value
 
End Sub
 
Private Sub UserForm_Initialize()
Dim Site As Range
Dim Printer_Type As Range
Dim ws As Worksheet
 
    Set ws = Worksheets("LookUp")
 
    Set Site = ws.Range("Site")
    
    Me.Sitebox.List = Site.Resize(Application.CountA(Site)).Value
            
    Set Printer_Type = ws.Range("Type")
    
    Me.Printer_Type_box.List = Printer_Type.Resize(Application.CountA(Printer_Type)).Value
        
End Sub

That's only for the 'amend' form.

PS I added code for the initialize event, mainly to get rid of the blanks in the site and type comboboxes - you can change it back to the old code if you want.