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:
Sort by date Sort by votes
dmt32 said:
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
Click to expand...
Hi dave, thats perfect it works!! THANK YOU!!!
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
PLwolves87 said:
Hi dave, thats perfect it works!! THANK YOU!!!
Click to expand...

Glad suggestion resolved your issue despite my advancing years I surprise myself sometimes!

Code looks very like one I may have done for someone in past - did you find in whilst searching I did I help you with it?


Dave
 
Upvote 0
dmt32 said:
Glad suggestion resolved your issue despite my advancing years I surprise myself sometimes!

Code looks very like one I may have done for someone in past - did you find in whilst searching I did I help you with it?


Dave
Click to expand...
Hi Dave, no you helped me previously, i have just run the code and got an error to line is highlighted yellow, it said it was a type mismatc? "If Not IsError(m) And Val(m) > 0 Then"

i


VBA Code: 
  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)
 
Last edited by a moderator:
Upvote 0
Dave,

date not found msbox doesnt apear if i select a date which isnt there, comes up with a RUNTIME ERROR 13 type mismatch

If Not IsError(m) And Val(m) > 0 Then
 
Upvote 0
Hi,
try this update

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) Then
        If 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
        End If
    Else
        MsgBox "Date Not Found", 48, "Not Found"
    End If
    
    'enable update button
    Form.CommandButton5.Enabled = EditRecord And Not IsError(m)
End Sub

If still an issue then helpful if could place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave
 
Upvote 0
dmt32 said:
Hi,
try this update

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) Then
        If 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
        End If
    Else
        MsgBox "Date Not Found", 48, "Not Found"
    End If
   
    'enable update button
    Form.CommandButton5.Enabled = EditRecord And Not IsError(m)
End Sub

If still an issue then helpful if could place copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave
Click to expand...
Hi Dave, youve done it again my friend, perfect!!
thanks
 
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