Results 1 to 8 of 8

USERFORM: DELETE command & correct code of EDIT/AMEND?

This is a discussion on USERFORM: DELETE command & correct code of EDIT/AMEND? within the Excel Questions forums, part of the Question Forums category; Made a great USERFORM database, current features are good including SEARCH/EDIT RECORD & ADD , however VB code edits needed: ...

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    115

    Default USERFORM: DELETE command & correct code of EDIT/AMEND?

    Made a great USERFORM database, current features are good including SEARCH/EDIT RECORD & ADD, however VB code edits needed:

    http://www.1sar.karoo.net/DB~ADD,EDIT&SEARCHcars.xls 248 KB's

    HOW TO CORRECT or CREATE THE FOLLOWING FEATURES:

    i). AMEND records feature, causes a DOUBLE ENTRY overwriting existing data in the "database" spreadsheet. WHY & HOW to correct this code?

    ii). USERFORM needs DELETE RECORD feature, possibly from with search feature, button next to AMEND SELECTION perhaps.

    iii). USERFORM needs BROWSE through RECORDS feature using NEXT & PREVIOUS BUTTONS would be a useful additional, whilst in SEARCH feature via SEARCH/EDIT RECORD.

  2. #2
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,280

    Default Re: USERFORM: DELETE command & correct code of EDIT/AMEND?

    You'll have better luck actually posting the code, as many people are blocked from external file sharing sites, or simply choose not to download files from untrusted sources.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    115

    Default Re: USERFORM: DELETE command & correct code of EDIT/AMEND?

    Ok, the hyperlinks work currently, I'd would have attached the file in the first place however the file size is greater then most xl forum allowed max attachment file size. Anyway as aforementioned the code needs editing for USERFORM3, below here is the VB code:
    i). AMEND records feature, causes a DOUBLE ENTRY overwriting existing data in the "database" spreadsheet. WHY & HOW to correct this code?
    ii). USERFORM needs DELETE RECORD feature, possibly from with search feature, button next to AMEND SELECTION perhaps.
    iii). USERFORM needs BROWSE through RECORDS feature using NEXT & PREVIOUS BUTTONS would be a useful additional, whilst in SEARCH feature via SEARCH/EDIT RECORD.

    Code:
    Dim Rw As Long
    Dim Cap As IntegerSub HighLight1(Ctrl As Control)
        With Ctrl
            If .Value = True Then
                .BackColor = RGB(255, 255, 0)
            Else
                .BackColor = vbButtonFace
            End If
        End With
    End Sub
    Sub HighLight2(Ctrl As Control)
        With Ctrl
            If .Value = True Then
                .BackColor = RGB(0, 255, 255)
            Else
                .BackColor = vbButtonFace
            End If
        End With
    End Sub
    Private Sub CheckBox1_Change()
        HighLight2 CheckBox1
    End Sub
    Private Sub CheckBox2_Click()
        HighLight2 CheckBox2
    End Sub
    Private Sub CheckBox3_Click()
        HighLight2 CheckBox3
    End Sub
    Private Sub cmdAmend_Click()
    Dim DataRw As Long
    For Cap = 1 To 28
    DataRw = Sheet5.Cells(Rw, 29).Value
    Sheet1.Cells(Rw, Cap).Value = Me("tbx" & Cap).Value
    Next Cap
    End Sub
    Private Sub CommandButton1_Click()
    'SEARCH
        Dim C As Variant
        Dim Col As Variant
        Dim Ctrl As Object
        Dim Data As Variant
        Dim DstWks As Worksheet
        Dim BtnNumber As Integer
        Dim FirstAddx As String
        Dim FoundIt As Range
        Dim i As Integer
        Dim R As Long
        Dim rng As Range
        Dim RngEnd As Range
        Dim SrcWks As Worksheet
        Set SrcWks = Worksheets("Data")
        Set DstWks = Worksheets("Search Details")
        With Frame1.Controls
            For i = 0 To .Count - 1
                If .Item(i).Value = True Then
                    BtnName = .Item(i).Name
                    Exit For
                End If
            Next i
        End With
        Select Case BtnName
        Case "OptionButton1"
            Col = 1: Data = TextBox1: GoSub DataSearch
            Col = 2: Data = TextBox2: GoSub DataSearch
        Case "OptionButton2"
            Col = 3: Data = TextBox1: GoSub DataSearch
        Case "OptionButton3"
            Col = 4: Data = TextBox2: GoSub DataSearch
        Case "OptionButton4"
            Col = 18: Data = TextBox2: GoSub DataSearch
        End Select
        Exit Sub
    DataSearch:
        With DstWks
            Set RngEnd = .Cells.Find("*", [A1], xlFormulas, xlWhole, xlByRows, xlPrevious, False)
            R = RngEnd.Row
            R = IIf(R < 2, 2, R + 1)
        End With
        With SrcWks
            Set rng = .Cells(2, Col)
            Set RngEnd = .Cells(Rows.Count, Col).End(xlUp)
            Set RngEnd = IIf(RngEnd.Row < rng.Row, rng, RngEnd)
            Set rng = .Range(rng, RngEnd)
        End With
        Data = Trim(Data)
        Set FoundIt = rng.Find(What:=Data, After:=rng.Cells(1, 1), _
                               LookIn:=xlFormulas, LookAt:=CheckBox2.Value + 2, _
                               SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=CheckBox1.Value)
        If Not FoundIt Is Nothing Then
            FirstAddx = FoundIt.Address
            FoundIt.EntireRow.Resize(1, 28).Copy Destination:=DstWks.Cells(R, 1)
            If CheckBox3.Value = False Then Exit Sub
            Do
                    DstWks.Cells(R, 29).Value = FoundIt.Row
                FoundIt.EntireRow.Resize(1, 28).Copy Destination:=DstWks.Cells(R, 1)
                Set FoundIt = rng.FindNext(FoundIt)
                R = R + 1
            Loop While FoundIt.Address <> FirstAddx And Not FoundIt Is Nothing
            Me.ListBox1.RowSource = DstWks.UsedRange.Address(external:=True)
            Me.Height = 425  ' 391
        Else
            MsgBox "No Match was found for '" & Data & " '", vbExclamation
        End If
    End Sub
    Private Sub CommandButton2_Click()
    'CLOSE
        Me.Hide
        Unload Me
    End Sub
    Private Sub CommandButton3_Click()
    'RESET FORM
        TextBox1.Value = ""
        TextBox2.Value = ""
        OptionButton1.Value = True
        CheckBox1.Value = False
        CheckBox2.Value = False
        CheckBox3.Value = True
        TextBox1.SetFocus
    End Sub
    Private Sub CommandButton4_Click()
    Dim tbl As Range
    Dim Answer As String
        Answer = MsgBox("This will remove any searches you have already done." & vbCrLf _
                      & "Are you sure you want to clear Search Details?", vbQuestion + vbYesNo)
        If Answer = vbYes Then
        Set tbl = Worksheets("Search Details").Range("A2").CurrentRegion
     tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
        tbl.Columns.Count).ClearContents
        End If
    End Sub
    Private Sub ListBox1_Click()
    Rw = Me.ListBox1.ListIndex + 1
    With Me
    .Width = 750    ' 548
        For Cap = 1 To 28
        Me("lbl" & Cap).Caption = Sheet5.Cells(1, Cap).Value
        Me("tbx" & Cap).Value = Sheet5.Cells(Rw, Cap).Value
        Next Cap
        End With
    End Sub
    Private Sub OptionButton1_Change()
        HighLight1 OptionButton1
    End Sub
    Private Sub OptionButton1_Click()
        If OptionButton1.Value = True Then
            Label1.Caption = "Car Manufacturer"
            TextBox1.Visible = True
            Label2.Caption = "Car Model"
            TextBox2.Visible = True
            TextBox1.SetFocus
        End If
    End Sub
    Private Sub OptionButton2_Change()
        HighLight1 OptionButton2
    End Sub
    Private Sub OptionButton2_Click()
        If OptionButton2.Value = True Then
            Label1.Caption = "Model Type"
            TextBox1.Visible = True
            Label2.Caption = ""
            TextBox2.Visible = False
            TextBox1.SetFocus
        End If
    End Sub
    Private Sub OptionButton3_Change()
        HighLight1 OptionButton3
    End Sub
    Private Sub OptionButton3_Click()
        If OptionButton3.Value = True Then
            Label1.Caption = ""
            TextBox1.Visible = False
            Label2.Caption = "Litre"
            TextBox2.Visible = True
            TextBox2.SetFocus
        End If
    End Sub
    Private Sub OptionButton4_Change()
        HighLight1 OptionButton4
    End Sub
    Private Sub OptionButton4_Click()
        If OptionButton4.Value = True Then
            Label1.Caption = ""
            TextBox1.Visible = False
            Label2.Caption = "Colour"
            TextBox2.Visible = True
            TextBox2.SetFocus
        End If
    End Sub
    Private Sub UserForm_Activate()
    Me.CommandButton3.Value = True
    End Sub
    Private Sub UserForm_Initialize()
    Me.Height = 220
    Me.Width = 384
    End Sub

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,910

    Default Re: USERFORM: DELETE command & correct code of EDIT/AMEND?

    Here's a free add-in that will do much of what you want. You could customize it if needed.

    J-Walk Enhanced Data Form v3
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    115

    Default Re: USERFORM: DELETE command & correct code of EDIT/AMEND?

    Quote Originally Posted by AlphaFrog View Post
    The only problem with that, being an ADD-IN, then XL files that rely on this feature I presume would not function correctly on other PC's with EXCEL, without the ADD-IN.

    Hence instead will continue to use COMMAND BUTTONS/USERFORM/VB CODE, easier to edit as well.

    VB code is not something I do often as I don't work in an office, I look for obvious references for rows/columns then edit them to my own needs. After all FORMULAs are relatively well defined in terms of commands and ranges, however VB code a blank page, hence I look at examples, find some good ones and copy & paste relevant userforms/vb code.

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,910

    Default Re: USERFORM: DELETE command & correct code of EDIT/AMEND?

    You could pay the small fee for the source code password and save the add-in as an .xls file then modify the code to suit. But it's your call. Just offering one possibility.

    Here's another source for applicable example code
    New Improved Excel Data Entry Form | Contextures Blog
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  7. #7
    Board Regular
    Join Date
    Jun 2010
    Posts
    115

    Default Re: USERFORM: DELETE command & correct code of EDIT/AMEND?

    Quote Originally Posted by AlphaFrog View Post
    pay fee for source code add-in. Here's another source New Improved Excel Data Entry Form | Contextures Blog
    Hi, really looking for an edit for my USERFORM, or another USERFORM that can be edited easily.

    The contextures isn't really a USERFORM solution, but the easiest to use/edit, and I've already done it to my examples:
    DB COMBO SEARCH A-Z consecutive, ADD/EDIT/BROWSE & SEARCH inc PHOTO

    See 3 HYPERLINKS following, an amalgamation of some great ideas :
    ~ COMBO BOX SEARCH which display PHOTOS on a separate tab called RESULTS.
    ~ ADD/UPDATE/DELETE/BROWSE/SEARCH records which automatically display available photos on a separate tab called INPUT.
    These spreadsheets are populated with example list of cars, but the data can be replaced by whatever, here are the 3 different types:


    i). COMBO SEARCH A-Z CONSECUTIVE VERTICAL BAR with 4 CATEGORIES R/H of screen with PHOTOS: File Size 1 MB
    http://www.1sar.karoo.net/exoftable3...editDELETE.xls

    ii). COMBO SEARCH A-Z CONSECUTIVE HORIZONTAL BAR with 4 CATEGORIES TOP of screen: File Size 1 MB
    http://www.1sar.karoo.net/exoftable3...editDELETE.xls

    iii). COMBO SEARCH A-Z CONSECUTIVE HORIZONTAL BAR with 11 CATEGORIES TOP of screen: File Size 1 MB
    http://www.1sar.karoo.net/exoftable3...editDELETE.xls

  8. #8
    Board Regular
    Join Date
    Jun 2010
    Posts
    115

    Cool Re: USERFORM: DELETE command & correct code of EDIT/AMEND?

    Here is my latest edit, another DATABASE version, with QUICK START macro command buttons, all with no faults for SEARCH & ADD/EDIT/UPDATE/BROWSE, even includes photo preview in SEARCH & ADD/EDIT/BROWSE for records in database with photos inserted.

    http://www.1sar.karoo.net/QUICKstartFRONTdb.xls 390 KB's

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com