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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
  • Solved
Show message box on userform after sum or subtraction
Replies
2
Views
111
Maklil
M
I
  • Question Question
Use the majority of an existing working code in new project advice
Replies
1
Views
219
ipbr21054
I
M
  • Question 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
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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