Userform code help with runtime error please

J

joespontiac

New Member
Joined
Aug 8, 2022
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello, firstly i am really new to using user forms and code and the one i have put together was by trawling the internet and figuring parts out.

So as an overview I have a database and a user form with a "search box". This is looking to find a match in column A but the range starts on row 3 through to row 5263 (this will get larger over time).
The rest of the code is for check boxes (putting 1 or 0 in the specified cells or date pickers upon clicking a "save" button to run the code).
It seem to work well but I have two (possibly related?) errors. Firstly, randomly i get a runtime error 1004 message that when i go to debug it highlights the line "If item_in_review = Sheets("SUBS WIP").Range("A" & row_number) Then". I htink this has to do with it going through to a cell out of range or something?
Secondly, sometimes I end up with data on a row much further down the sheet rather than on the correct row?

Any help would be appreciated


VBA Code: 
Option Compare Text

Private Sub CommandButton1_Click()


row_number = 2
Do
DoEvents
row_number = row_number + 1
item_in_review = TextBox1.Text
    If item_in_review = Sheets("SUBS WIP").Range("A" & row_number) Then

Serial.Text = Sheets("SUBS WIP").Range("D" & row_number)
BATCH.Text = Sheets("SUBS WIP").Range("E" & row_number)
GEN.Text = Sheets("SUBS WIP").Range("F" & row_number)
CAT.Text = Sheets("SUBS WIP").Range("G" & row_number)
STAGE.Text = Sheets("SUBS WIP").Range("BA" & row_number)
MSDate.Value = Sheets("SUBS WIP").Range("AP" & row_number)
RSDate.Value = Sheets("SUBS WIP").Range("AR" & row_number)
ASDate.Value = Sheets("SUBS WIP").Range("AT" & row_number)
FDate.Value = Sheets("SUBS WIP").Range("AK" & row_number)
SDate.Value = Sheets("SUBS WIP").Range("AL" & row_number)
Scrapped.Text = Sheets("SUBS WIP").Range("AM" & row_number)
SAPClosed.Text = Sheets("SUBS WIP").Range("AO" & row_number)
MRDate.Text = Sheets("SUBS WIP").Range("AQ" & row_number)
WRDate.Text = Sheets("SUBS WIP").Range("AS" & row_number)
ARDate.Text = Sheets("SUBS WIP").Range("AU" & row_number)
TextBox3.Text = Sheets("SUBS WIP").Range("BK" & row_number)

    
        Survey.Value = Sheets("SUBS WIP").Range("L" & row_number)
        Decan.Value = Sheets("SUBS WIP").Range("M" & row_number)
        Strip.Value = Sheets("SUBS WIP").Range("I" & row_number)
        MicroSent.Value = Sheets("SUBS WIP").Range("J" & row_number)
        MicroRet.Value = Sheets("SUBS WIP").Range("K" & row_number)
        Rewind.Value = Sheets("SUBS WIP").Range("N" & row_number)
        Grind.Value = Sheets("SUBS WIP").Range("O" & row_number)
        SMIss.Value = Sheets("SUBS WIP").Range("P" & row_number)
        SMRet.Value = Sheets("SUBS WIP").Range("Q" & row_number)
        ShortBuild.Value = Sheets("SUBS WIP").Range("R" & row_number)
        WeldSent.Value = Sheets("SUBS WIP").Range("S" & row_number)
        WeldRet.Value = Sheets("SUBS WIP").Range("T" & row_number)
        LMIss.Value = Sheets("SUBS WIP").Range("U" & row_number)
        LMRet.Value = Sheets("SUBS WIP").Range("V" & row_number)
            Survey2.Value = Sheets("SUBS WIP").Range("W" & row_number)
            Kitted.Value = Sheets("SUBS WIP").Range("X" & row_number)
            AEMSent.Value = Sheets("SUBS WIP").Range("Y" & row_number)
            AEMRet.Value = Sheets("SUBS WIP").Range("Z" & row_number)
                Survey3.Value = Sheets("SUBS WIP").Range("AA" & row_number)
                Strip2.Value = Sheets("SUBS WIP").Range("AB" & row_number)
                Overhaul.Value = Sheets("SUBS WIP").Range("AC" & row_number)
                BalSpin.Value = Sheets("SUBS WIP").Range("AD" & row_number)
                    Survey4.Value = Sheets("SUBS WIP").Range("AE" & row_number)
                    Strip3.Value = Sheets("SUBS WIP").Range("AF" & row_number)
                    Rewind2.Value = Sheets("SUBS WIP").Range("AG" & row_number)
                    Build.Value = Sheets("SUBS WIP").Range("AH" & row_number)
                        Finaled.Value = Sheets("SUBS WIP").Range("AI" & row_number)
                        Scrap.Value = Sheets("SUBS WIP").Range("AJ" & row_number)
                        ISSUE.Value = Sheets("SUBS WIP").Range("H" & row_number)
                        THIRDPARTY.Value = Sheets("SUBS WIP").Range("BJ" & row_number)

        
    End If
    Loop Until item_in_review = ""
    

End Sub





Private Sub CommandButton2_Click()

MsgBox "Well Done Team!"

row_number = 2
Do
DoEvents
row_number = row_number + 1
item_in_review = TextBox1.Text
    If item_in_review = Sheets("SUBS WIP").Range("A" & row_number) Then

        Sheets("SUBS WIP").Range("L" & row_number) = -Survey.Value
        Sheets("SUBS WIP").Range("M" & row_number) = -Decan.Value
        Sheets("SUBS WIP").Range("I" & row_number) = -Strip.Value
        Sheets("SUBS WIP").Range("J" & row_number) = -MicroSent.Value
        Sheets("SUBS WIP").Range("K" & row_number) = -MicroRet.Value
        Sheets("SUBS WIP").Range("N" & row_number) = -Rewind.Value
        Sheets("SUBS WIP").Range("O" & row_number) = -Grind.Value
        Sheets("SUBS WIP").Range("P" & row_number) = -SMIss.Value
        Sheets("SUBS WIP").Range("Q" & row_number) = -SMRet.Value
        Sheets("SUBS WIP").Range("R" & row_number) = -ShortBuild.Value
        Sheets("SUBS WIP").Range("S" & row_number) = -WeldSent.Value
        Sheets("SUBS WIP").Range("T" & row_number) = -WeldRet.Value
        Sheets("SUBS WIP").Range("U" & row_number) = -LMIss.Value
        Sheets("SUBS WIP").Range("V" & row_number) = -LMRet.Value
            Sheets("SUBS WIP").Range("W" & row_number) = -Survey2.Value
            Sheets("SUBS WIP").Range("X" & row_number) = -Kitted.Value
            Sheets("SUBS WIP").Range("Y" & row_number) = -AEMSent.Value
            Sheets("SUBS WIP").Range("Z" & row_number) = -AEMRet.Value
                Sheets("SUBS WIP").Range("AA" & row_number) = -Survey3.Value
                Sheets("SUBS WIP").Range("AB" & row_number) = -Strip2.Value
                Sheets("SUBS WIP").Range("AC" & row_number) = -Overhaul.Value
                Sheets("SUBS WIP").Range("AD" & row_number) = -BalSpin.Value
                    Sheets("SUBS WIP").Range("AE" & row_number) = -Survey4.Value
                    Sheets("SUBS WIP").Range("AF" & row_number) = -Strip3.Value
                    Sheets("SUBS WIP").Range("AG" & row_number) = -Rewind2.Value
                    Sheets("SUBS WIP").Range("AH" & row_number) = -Build.Value
                        Sheets("SUBS WIP").Range("AI" & row_number) = -Finaled.Value
                        Sheets("SUBS WIP").Range("AJ" & row_number) = -Scrap.Value
                        Sheets("SUBS WIP").Range("H" & row_number) = -ISSUE.Value
                        Sheets("SUBS WIP").Range("BJ" & row_number) = -THIRDPARTY.Value
                        Sheets("SUBS WIP").Range("BK" & row_number) = TextBox3.Value
                        
                        
Sheets("SUBS WIP").Range("AP" & row_number) = MSDate.Value
Sheets("SUBS WIP").Range("AR" & row_number) = RSDate.Value
Sheets("SUBS WIP").Range("AT" & row_number) = ASDate.Value
Sheets("SUBS WIP").Range("AK" & row_number) = FDate.Value
Sheets("SUBS WIP").Range("AL" & row_number) = SDate.Value


For Each ctrl In Me.Controls

       Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
             Case "CheckBox"
                ctrl.Value = False
                

        End Select

Next

TextBox1.SetFocus

    End If
    
    
Loop Until item_in_review = ""



End Sub

Private Sub CommandButton4_Click()


For Each ctrl In Me.Controls

       Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
             Case "CheckBox"
                ctrl.Value = False
                

        End Select

Next

TextBox1.SetFocus


End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
I should also mention that this is a shared file if that makes any difference at all
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
can't execute code in break mode!!
Replies
2
Views
2K
Mayozero
M
M
  • Solved
How to fix formats for the Listbox view in a userform?
Replies
5
Views
1K
Mayozero
M
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
502
pure vito
P
P
  • Question
Please Help Fixing VBA Code
Replies
1
Views
178
pure vito
P
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
325
pure vito
P

Forum statistics

Threads
1,214,904
Messages
6,122,169
Members
449,070
Latest member
webster33

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