USERFORM button for UPDATE error "block if without end if"

P

PLwolves87

New Member
Joined
Jan 6, 2023
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Hi All,

so my userform search and display button works and brings back the correct data, but i have now made a UPDATE button but when i click on the button i get an error " BLOCK IF WITHOUT END IF" the data which i want updating doesnt update at the moment because of this error, hope you can help, please see below code.

if someone knows a better way to code this please feel free. :)

VBA Code: 
Private Sub CommandButton5_Click()

Dim x As Long
Dim y As Long
targetsheet = Account1.Value & Account2.Value
If targetsheet = "" Then


x = Worksheets(targetsheet).Range("A" & Rows.Count).End(xlUp).Row
For y = 3 To x

If Worksheets(targetsheet).Cells(y, 1).Value = TextBox1.Value Then
Worksheets(targetsheet).Cells(y, 2) = TextBox47.Value
Worksheets(targetsheet).Cells(y, 3) = TextBox2.Value
Worksheets(targetsheet).Cells(y, 4) = TextBox3.Value
Worksheets(targetsheet).Cells(y, 5) = TextBox4.Value
Worksheets(targetsheet).Cells(y, 6) = TextBox5.Value
Worksheets(targetsheet).Cells(y, 7) = TextBox6.Value
Worksheets(targetsheet).Cells(y, 8) = TextBox7.Value
Worksheets(targetsheet).Cells(y, 10) = TextBox8.Value
Worksheets(targetsheet).Cells(y, 11) = TextBox9.Value
Worksheets(targetsheet).Cells(y, 12) = TextBox10.Value
Worksheets(targetsheet).Cells(y, 13) = TextBox11.Value
Worksheets(targetsheet).Cells(y, 14) = TextBox12.Value
Worksheets(targetsheet).Cells(y, 15) = TextBox13.Value
Worksheets(targetsheet).Cells(y, 16) = TextBox14.Value
Worksheets(targetsheet).Cells(y, 17) = TextBox15.Value
Worksheets(targetsheet).Cells(y, 18) = TextBox16.Value
Worksheets(targetsheet).Cells(y, 19) = TextBox17.Value
Worksheets(targetsheet).Cells(y, 20) = TextBox18.Value
Worksheets(targetsheet).Cells(y, 21) = TextBox19.Value
Worksheets(targetsheet).Cells(y, 22) = TextBox20.Value
Worksheets(targetsheet).Cells(y, 23) = TextBox21.Value
Worksheets(targetsheet).Cells(y, 24) = TextBox22.Value
Worksheets(targetsheet).Cells(y, 25) = TextBox23.Value
Worksheets(targetsheet).Cells(y, 26) = TextBox24.Value
Worksheets(targetsheet).Cells(y, 27) = TextBox25.Value
Worksheets(targetsheet).Cells(y, 28) = TextBox26.Value
Worksheets(targetsheet).Cells(y, 29) = TextBox27.Value
Worksheets(targetsheet).Cells(y, 30) = TextBox28.Value
Worksheets(targetsheet).Cells(y, 31) = TextBox29.Value
Worksheets(targetsheet).Cells(y, 32) = TextBox30.Value
Worksheets(targetsheet).Cells(y, 33) = TextBox31.Value
Worksheets(targetsheet).Cells(y, 34) = TextBox32.Value
Worksheets(targetsheet).Cells(y, 35) = TextBox33.Value
Worksheets(targetsheet).Cells(y, 36) = TextBox34.Value
Worksheets(targetsheet).Cells(y, 37) = TextBox35.Value
Worksheets(targetsheet).Cells(y, 38) = TextBox36.Value
Worksheets(targetsheet).Cells(y, 39) = TextBox37.Value
Worksheets(targetsheet).Cells(y, 40) = TextBox38.Value
Worksheets(targetsheet).Cells(y, 41) = TextBox39.Value
Worksheets(targetsheet).Cells(y, 42) = TextBox40.Value
Worksheets(targetsheet).Cells(y, 43) = TextBox41.Value
Worksheets(targetsheet).Cells(y, 44) = TextBox42.Value
Worksheets(targetsheet).Cells(y, 45) = TextBox43.Value
Worksheets(targetsheet).Cells(y, 46) = TextBox44.Value
Worksheets(targetsheet).Cells(y, 47) = TextBox45.Value
Worksheets(targetsheet).Cells(y, 48) = TextBox46.Value



End If
Next y
MsgBox "update complete"
End Sub
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
You forget to close first if statement that you've opened before for loop. This will work
VBA Code: 
Private Sub CommandButton5_Click()

Dim x As Long
Dim y As Long
targetsheet = Account1.Value & Account2.Value
If targetsheet = "" Then 'Statement begins


x = Worksheets(targetsheet).Range("A" & Rows.Count).End(xlUp).Row
For y = 3 To x

If Worksheets(targetsheet).Cells(y, 1).Value = TextBox1.Value Then
Worksheets(targetsheet).Cells(y, 2) = TextBox47.Value
Worksheets(targetsheet).Cells(y, 3) = TextBox2.Value
Worksheets(targetsheet).Cells(y, 4) = TextBox3.Value
Worksheets(targetsheet).Cells(y, 5) = TextBox4.Value
Worksheets(targetsheet).Cells(y, 6) = TextBox5.Value
Worksheets(targetsheet).Cells(y, 7) = TextBox6.Value
Worksheets(targetsheet).Cells(y, 8) = TextBox7.Value
Worksheets(targetsheet).Cells(y, 10) = TextBox8.Value
Worksheets(targetsheet).Cells(y, 11) = TextBox9.Value
Worksheets(targetsheet).Cells(y, 12) = TextBox10.Value
Worksheets(targetsheet).Cells(y, 13) = TextBox11.Value
Worksheets(targetsheet).Cells(y, 14) = TextBox12.Value
Worksheets(targetsheet).Cells(y, 15) = TextBox13.Value
Worksheets(targetsheet).Cells(y, 16) = TextBox14.Value
Worksheets(targetsheet).Cells(y, 17) = TextBox15.Value
Worksheets(targetsheet).Cells(y, 18) = TextBox16.Value
Worksheets(targetsheet).Cells(y, 19) = TextBox17.Value
Worksheets(targetsheet).Cells(y, 20) = TextBox18.Value
Worksheets(targetsheet).Cells(y, 21) = TextBox19.Value
Worksheets(targetsheet).Cells(y, 22) = TextBox20.Value
Worksheets(targetsheet).Cells(y, 23) = TextBox21.Value
Worksheets(targetsheet).Cells(y, 24) = TextBox22.Value
Worksheets(targetsheet).Cells(y, 25) = TextBox23.Value
Worksheets(targetsheet).Cells(y, 26) = TextBox24.Value
Worksheets(targetsheet).Cells(y, 27) = TextBox25.Value
Worksheets(targetsheet).Cells(y, 28) = TextBox26.Value
Worksheets(targetsheet).Cells(y, 29) = TextBox27.Value
Worksheets(targetsheet).Cells(y, 30) = TextBox28.Value
Worksheets(targetsheet).Cells(y, 31) = TextBox29.Value
Worksheets(targetsheet).Cells(y, 32) = TextBox30.Value
Worksheets(targetsheet).Cells(y, 33) = TextBox31.Value
Worksheets(targetsheet).Cells(y, 34) = TextBox32.Value
Worksheets(targetsheet).Cells(y, 35) = TextBox33.Value
Worksheets(targetsheet).Cells(y, 36) = TextBox34.Value
Worksheets(targetsheet).Cells(y, 37) = TextBox35.Value
Worksheets(targetsheet).Cells(y, 38) = TextBox36.Value
Worksheets(targetsheet).Cells(y, 39) = TextBox37.Value
Worksheets(targetsheet).Cells(y, 40) = TextBox38.Value
Worksheets(targetsheet).Cells(y, 41) = TextBox39.Value
Worksheets(targetsheet).Cells(y, 42) = TextBox40.Value
Worksheets(targetsheet).Cells(y, 43) = TextBox41.Value
Worksheets(targetsheet).Cells(y, 44) = TextBox42.Value
Worksheets(targetsheet).Cells(y, 45) = TextBox43.Value
Worksheets(targetsheet).Cells(y, 46) = TextBox44.Value
Worksheets(targetsheet).Cells(y, 47) = TextBox45.Value
Worksheets(targetsheet).Cells(y, 48) = TextBox46.Value



End If
Next y
End If 'Statement ends
MsgBox "update complete"
End Sub
Are you missing Cells(y, 9) intentionally?
 
Upvote 0
Flashbond said:
You forget to close first if statement that you've opened before for loop. This will work
VBA Code: 
Private Sub CommandButton5_Click()

Dim x As Long
Dim y As Long
targetsheet = Account1.Value & Account2.Value
If targetsheet = "" Then 'Statement begins


x = Worksheets(targetsheet).Range("A" & Rows.Count).End(xlUp).Row
For y = 3 To x

If Worksheets(targetsheet).Cells(y, 1).Value = TextBox1.Value Then
Worksheets(targetsheet).Cells(y, 2) = TextBox47.Value
Worksheets(targetsheet).Cells(y, 3) = TextBox2.Value
Worksheets(targetsheet).Cells(y, 4) = TextBox3.Value
Worksheets(targetsheet).Cells(y, 5) = TextBox4.Value
Worksheets(targetsheet).Cells(y, 6) = TextBox5.Value
Worksheets(targetsheet).Cells(y, 7) = TextBox6.Value
Worksheets(targetsheet).Cells(y, 8) = TextBox7.Value
Worksheets(targetsheet).Cells(y, 10) = TextBox8.Value
Worksheets(targetsheet).Cells(y, 11) = TextBox9.Value
Worksheets(targetsheet).Cells(y, 12) = TextBox10.Value
Worksheets(targetsheet).Cells(y, 13) = TextBox11.Value
Worksheets(targetsheet).Cells(y, 14) = TextBox12.Value
Worksheets(targetsheet).Cells(y, 15) = TextBox13.Value
Worksheets(targetsheet).Cells(y, 16) = TextBox14.Value
Worksheets(targetsheet).Cells(y, 17) = TextBox15.Value
Worksheets(targetsheet).Cells(y, 18) = TextBox16.Value
Worksheets(targetsheet).Cells(y, 19) = TextBox17.Value
Worksheets(targetsheet).Cells(y, 20) = TextBox18.Value
Worksheets(targetsheet).Cells(y, 21) = TextBox19.Value
Worksheets(targetsheet).Cells(y, 22) = TextBox20.Value
Worksheets(targetsheet).Cells(y, 23) = TextBox21.Value
Worksheets(targetsheet).Cells(y, 24) = TextBox22.Value
Worksheets(targetsheet).Cells(y, 25) = TextBox23.Value
Worksheets(targetsheet).Cells(y, 26) = TextBox24.Value
Worksheets(targetsheet).Cells(y, 27) = TextBox25.Value
Worksheets(targetsheet).Cells(y, 28) = TextBox26.Value
Worksheets(targetsheet).Cells(y, 29) = TextBox27.Value
Worksheets(targetsheet).Cells(y, 30) = TextBox28.Value
Worksheets(targetsheet).Cells(y, 31) = TextBox29.Value
Worksheets(targetsheet).Cells(y, 32) = TextBox30.Value
Worksheets(targetsheet).Cells(y, 33) = TextBox31.Value
Worksheets(targetsheet).Cells(y, 34) = TextBox32.Value
Worksheets(targetsheet).Cells(y, 35) = TextBox33.Value
Worksheets(targetsheet).Cells(y, 36) = TextBox34.Value
Worksheets(targetsheet).Cells(y, 37) = TextBox35.Value
Worksheets(targetsheet).Cells(y, 38) = TextBox36.Value
Worksheets(targetsheet).Cells(y, 39) = TextBox37.Value
Worksheets(targetsheet).Cells(y, 40) = TextBox38.Value
Worksheets(targetsheet).Cells(y, 41) = TextBox39.Value
Worksheets(targetsheet).Cells(y, 42) = TextBox40.Value
Worksheets(targetsheet).Cells(y, 43) = TextBox41.Value
Worksheets(targetsheet).Cells(y, 44) = TextBox42.Value
Worksheets(targetsheet).Cells(y, 45) = TextBox43.Value
Worksheets(targetsheet).Cells(y, 46) = TextBox44.Value
Worksheets(targetsheet).Cells(y, 47) = TextBox45.Value
Worksheets(targetsheet).Cells(y, 48) = TextBox46.Value



End If
Next y
End If 'Statement ends
MsgBox "update complete"
End Sub
Are you missing Cells(y, 9) intentionally?
Click to expand...
Hi thanks for this, so the error has stopped, and i didnt realise i had missed cell y,9 so thanks for this, the only problem i have is its still not updating the spreadsheet when i click the button. so all of the data appears when i click the search button, then lets say i want to change the information in textbox2,textbox3,textbox4 and textbox5 and then click update button nothing happens, i get the msgbox say update complete but the data hasnt updated?
 
Upvote 0
Flashbond said:
You forget to close first if statement that you've opened before for loop.
Click to expand...

this is my full code,

commandbutton4 = SEARCH BUTTON
commandbutton5= UPDATE BUTTON

VBA Code: 
Sub GetValues(ByVal sh As Object, ByVal SearchBox As Object, StartBox As Long)
    Dim c           As Long
    Dim Search      As Variant, m As Variant
    Dim Form        As Object
 
    Set Form = SearchBox.Parent
    Search = SearchBox.Value
 
    If Len(Search) > 0 Then
        'ensure entry is a date                   'corece string date to long        'invalid date entry
        If IsDate(Search) Then Search = CLng(DateValue(Search)) Else MsgBox "Invalid Date Entry", 16, "Invalid Entry": Exit Sub
    Else
        Exit Sub
   End If
 
    'find date in range
    m = Application.Match(Search, sh.Columns(1), 0)
 
    c = 3
    If Not IsError(m) Then
        'populate textboxes
        m = CLng(m)
        For i = StartBox To StartBox + 44: Form.Controls("TextBox" & i).Value = sh.Cells(m, c).Value: c = c + 1: Next i
        Else: MsgBox "Date Not Found", 48, "Not Found"
        End If
    
      
        End Sub

Private Sub CommandButton4_Click()
 Dim sh          As Worksheet
 
    Set sh = ThisWorkbook.Worksheets(Me.Account1.Value & Me.Account2.Value)
    
 
    GetValues sh, Me.TextBox1, 2
  
 
End Sub


Private Sub CommandButton5_Click()
Dim x As Long
Dim y As Long
targetsheet = Account1.Value & Account2.Value
If targetsheet = "" Then 'Statement begins


x = Worksheets(targetsheet).Range("A" & Rows.Count).End(xlUp).Row
For y = 3 To x

If Worksheets(targetsheet).Cells(y, 1).Value = TextBox1.Value Then
Worksheets(targetsheet).Cells(y, 2) = TextBox47.Value
Worksheets(targetsheet).Cells(y, 3) = TextBox2.Value
Worksheets(targetsheet).Cells(y, 4) = TextBox3.Value
Worksheets(targetsheet).Cells(y, 5) = TextBox4.Value
Worksheets(targetsheet).Cells(y, 6) = TextBox5.Value
Worksheets(targetsheet).Cells(y, 7) = TextBox6.Value
Worksheets(targetsheet).Cells(y, 8) = TextBox7.Value
Worksheets(targetsheet).Cells(y, 9) = TextBox8.Value
Worksheets(targetsheet).Cells(y, 10) = TextBox9.Value
Worksheets(targetsheet).Cells(y, 11) = TextBox10.Value
Worksheets(targetsheet).Cells(y, 12) = TextBox11.Value
Worksheets(targetsheet).Cells(y, 13) = TextBox12.Value
Worksheets(targetsheet).Cells(y, 14) = TextBox13.Value
Worksheets(targetsheet).Cells(y, 15) = TextBox14.Value
Worksheets(targetsheet).Cells(y, 16) = TextBox15.Value
Worksheets(targetsheet).Cells(y, 17) = TextBox16.Value
Worksheets(targetsheet).Cells(y, 18) = TextBox17.Value
Worksheets(targetsheet).Cells(y, 19) = TextBox18.Value
Worksheets(targetsheet).Cells(y, 20) = TextBox19.Value
Worksheets(targetsheet).Cells(y, 21) = TextBox20.Value
Worksheets(targetsheet).Cells(y, 22) = TextBox21.Value
Worksheets(targetsheet).Cells(y, 23) = TextBox22.Value
Worksheets(targetsheet).Cells(y, 24) = TextBox23.Value
Worksheets(targetsheet).Cells(y, 25) = TextBox24.Value
Worksheets(targetsheet).Cells(y, 26) = TextBox25.Value
Worksheets(targetsheet).Cells(y, 27) = TextBox26.Value
Worksheets(targetsheet).Cells(y, 28) = TextBox27.Value
Worksheets(targetsheet).Cells(y, 29) = TextBox28.Value
Worksheets(targetsheet).Cells(y, 30) = TextBox29.Value
Worksheets(targetsheet).Cells(y, 31) = TextBox30.Value
Worksheets(targetsheet).Cells(y, 32) = TextBox31.Value
Worksheets(targetsheet).Cells(y, 33) = TextBox32.Value
Worksheets(targetsheet).Cells(y, 34) = TextBox33.Value
Worksheets(targetsheet).Cells(y, 35) = TextBox34.Value
Worksheets(targetsheet).Cells(y, 36) = TextBox35.Value
Worksheets(targetsheet).Cells(y, 37) = TextBox36.Value
Worksheets(targetsheet).Cells(y, 38) = TextBox37.Value
Worksheets(targetsheet).Cells(y, 39) = TextBox38.Value
Worksheets(targetsheet).Cells(y, 40) = TextBox39.Value
Worksheets(targetsheet).Cells(y, 41) = TextBox40.Value
Worksheets(targetsheet).Cells(y, 42) = TextBox41.Value
Worksheets(targetsheet).Cells(y, 43) = TextBox42.Value
Worksheets(targetsheet).Cells(y, 44) = TextBox43.Value
Worksheets(targetsheet).Cells(y, 45) = TextBox44.Value
Worksheets(targetsheet).Cells(y, 46) = TextBox45.Value
Worksheets(targetsheet).Cells(y, 47) = TextBox46.Value



Account1.Value = ""
Account2.Value = ""
TextBox1.Value = ""
TextBox34.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox35.Value = ""
TextBox10.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox13.Value = ""
TextBox14.Value = ""
TextBox15.Value = ""
TextBox16.Value = ""
TextBox17.Value = ""
TextBox36.Value = ""
TextBox18.Value = ""
TextBox19.Value = ""
TextBox20.Value = ""
TextBox21.Value = ""
TextBox22.Value = ""
TextBox23.Value = ""
TextBox24.Value = ""
TextBox25.Value = ""
TextBox37.Value = ""
TextBox26.Value = ""
TextBox27.Value = ""
TextBox28.Value = ""
TextBox29.Value = ""
TextBox30.Value = ""
TextBox31.Value = ""
TextBox32.Value = ""
TextBox33.Value = ""
TextBox38.Value = ""
TextBox39.Value = ""
TextBox40.Value = ""
TextBox41.Value = ""
TextBox42.Value = ""
TextBox43.Value = ""
TextBox44.Value = ""
TextBox45.Value = ""
TextBox46.Value = ""
TextBox47.Value = ""


End If
Next y
End If 'Statement ends
MsgBox "update complete"
End Sub
 
Last edited by a moderator:
Upvote 0
You have to check these two values if they are making a valid sheetname:

Account1.Value & Account2.Value
 
Upvote 0
Please use code tags when posting code - especially that much code.

If the sheet name were wrong you would get an error, so it's more likely that this condition:

Code: 
If Worksheets(targetsheet).Cells(y, 1).Value = TextBox1.Value Then

is not being met. What kind of data is in column A - text or numbers (or something else)?
 
Upvote 0
RoryA said:
Please use code tags when posting code - especially that much code.

If the sheet name were wrong you would get an error, so it's more likely that this condition:

Code: 
If Worksheets(targetsheet).Cells(y, 1).Value = TextBox1.Value Then

is not being met. What kind of data is in column A - text or numbers (or something else)?
Click to expand...
sorry will post tags going forward.

column A is date format 21/03/2023, data starts from row 3 as row 1&2 are headers. the search button works perfectly but when i change a data in a textbox and then click the update button nothing is updated.

VBA Code: 
Private Sub CommandButton5_Click()
Dim x As Long
Dim y As Long
targetsheet = Account1.Value & Account2.Value 'worksheet is selected from comboxboxes (combobox1=Account1 & combobox2=Account2)
If targetsheet = "" Then



x = Worksheets(targetsheet).Range("A" & Rows.Count).End(xlUp).Row 'when clicking the update button it looks at the worksheet selected in the above and then transfers/updates to the below
For y = 3 To x ' my data starts from ("A3") row 3

'is the below going to the correct cells, column 1 and so on

If Worksheets(targetsheet).Cells(y, 1).Value = TextBox1.Value Then
Worksheets(targetsheet).Cells(y, 2) = TextBox47.Value
Worksheets(targetsheet).Cells(y, 3) = TextBox2.Value
Worksheets(targetsheet).Cells(y, 4) = TextBox3.Value
Worksheets(targetsheet).Cells(y, 5) = TextBox4.Value
Worksheets(targetsheet).Cells(y, 6) = TextBox5.Value
Worksheets(targetsheet).Cells(y, 7) = TextBox6.Value
Worksheets(targetsheet).Cells(y, 8) = TextBox7.Value
Worksheets(targetsheet).Cells(y, 9) = TextBox8.Value
Worksheets(targetsheet).Cells(y, 10) = TextBox9.Value
Worksheets(targetsheet).Cells(y, 11) = TextBox10.Value
Worksheets(targetsheet).Cells(y, 12) = TextBox11.Value
Worksheets(targetsheet).Cells(y, 13) = TextBox12.Value
Worksheets(targetsheet).Cells(y, 14) = TextBox13.Value
Worksheets(targetsheet).Cells(y, 15) = TextBox14.Value
Worksheets(targetsheet).Cells(y, 16) = TextBox15.Value
Worksheets(targetsheet).Cells(y, 17) = TextBox16.Value
Worksheets(targetsheet).Cells(y, 18) = TextBox17.Value
Worksheets(targetsheet).Cells(y, 19) = TextBox18.Value
Worksheets(targetsheet).Cells(y, 20) = TextBox19.Value
Worksheets(targetsheet).Cells(y, 21) = TextBox20.Value
Worksheets(targetsheet).Cells(y, 22) = TextBox21.Value
Worksheets(targetsheet).Cells(y, 23) = TextBox22.Value
Worksheets(targetsheet).Cells(y, 24) = TextBox23.Value
Worksheets(targetsheet).Cells(y, 25) = TextBox24.Value
Worksheets(targetsheet).Cells(y, 26) = TextBox25.Value
Worksheets(targetsheet).Cells(y, 27) = TextBox26.Value
Worksheets(targetsheet).Cells(y, 28) = TextBox27.Value
Worksheets(targetsheet).Cells(y, 29) = TextBox28.Value
Worksheets(targetsheet).Cells(y, 30) = TextBox29.Value
Worksheets(targetsheet).Cells(y, 31) = TextBox30.Value
Worksheets(targetsheet).Cells(y, 32) = TextBox31.Value
Worksheets(targetsheet).Cells(y, 33) = TextBox32.Value
Worksheets(targetsheet).Cells(y, 34) = TextBox33.Value
Worksheets(targetsheet).Cells(y, 35) = TextBox34.Value
Worksheets(targetsheet).Cells(y, 36) = TextBox35.Value
Worksheets(targetsheet).Cells(y, 37) = TextBox36.Value
Worksheets(targetsheet).Cells(y, 38) = TextBox37.Value
Worksheets(targetsheet).Cells(y, 39) = TextBox38.Value
Worksheets(targetsheet).Cells(y, 40) = TextBox39.Value
Worksheets(targetsheet).Cells(y, 41) = TextBox40.Value
Worksheets(targetsheet).Cells(y, 42) = TextBox41.Value
Worksheets(targetsheet).Cells(y, 43) = TextBox42.Value
Worksheets(targetsheet).Cells(y, 44) = TextBox43.Value
Worksheets(targetsheet).Cells(y, 45) = TextBox44.Value
Worksheets(targetsheet).Cells(y, 46) = TextBox45.Value
Worksheets(targetsheet).Cells(y, 47) = TextBox46.Value
 
Last edited by a moderator:
Upvote 0
Your textbox contains text not a real date value, which is probably why the comparison doesn't work. You can use CDate to convert the text to a date using your regional settings:

VBA Code: 
If Worksheets(targetsheet).Cells(y, 1).Value = CDate(TextBox1.Value) Then

Note that if you will not have more than one matching entry, there are faster ways to do this.
 
Upvote 0
Hi,
If your GetValues code is returning the correct record to the Textboxes then I would have thought that you should be able to modify it to post the record back to the row.

UNTESTED but see if these updated codes will do what you want

VBA Code: 
Sub GetValues(ByVal SearchBox As Object, StartBox As Long, Optional ByVal EditRecord As Boolean)
    Dim c          As Long
    Dim Search     As Variant, m As Variant
    Dim Form       As Object
    Dim sh         As Worksheet
  
    Set Form = SearchBox.Parent
    Search = SearchBox.Value
  
    Set sh = ThisWorkbook.Worksheets(Form.Account1.Value & Form.Account2.Value)
  
    If Len(Search) > 0 Then
        'ensure entry is a date                   'corece string date to long        'invalid date entry
        If IsDate(Search) Then Search = CLng(DateValue(Search)) Else MsgBox "Invalid Date Entry", 16, "Invalid Entry": Exit Sub
    Else
        Exit Sub
    End If
  
    'find date in range
    m = IIf(EditRecord, Application.Match(Search, sh.Columns(1), 0), SearchBox.Tag)
  
    c = 3
    If Not IsError(m) And Val(m) > 0 Then
        'populate textboxes
        m = CLng(m)
        For i = StartBox To StartBox + 44
            With Form.Controls("TextBox" & i)
                If EditRecord = True Then
                    'get record
                    .Value = sh.Cells(m, c).Value
                Else
                    'post updated record to range
                    sh.Cells(m, c).Value = .Value
                    'clear control
                    .Value = ""
                End If
                c = c + 1
             End With
            Next i
        'inform user                                                                                                                                'tag matched record
        If Not EditRecord Then MsgBox "Record Updated", 64, "Updated": SearchBox.Tag = 0 Else SearchBox.Tag = m
    Else
        MsgBox "Date Not Found", 48, "Not Found"
    End If
  
        'enable update button
        Form.CommandButton5.Enabled = EditRecord And Not IsError(m)
End Sub

CommandButton Codes

VBA Code: 
Private Sub CommandButton4_Click()
    'get record
    GetValues Me.TextBox1, 2, True
End Sub

Private Sub CommandButton5_Click()
    'update record
    GetValues Me.TextBox1, 2
End Sub

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Solved
Entering information in the Userform
Replies
2
Views
650
KlausW
KlausW
J
  • Solved
How to return focus to Userform textbox after process is finished.
Replies
1
Views
956
jvoss
J
M
  • Question Question
Userform to add, chage status and show all available products.
Replies
2
Views
1K
mir994stan
M
M
  • Question Question
drag and drop listbox that updates at worksheet
Replies
2
Views
832
monvarona
M
A
  • Question Question
Dynamic Controls and Objects at Runtime
Replies
1
Views
2K
AgentSmith2000
A

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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