VBA Question or otherwise

KRKComputers · Nov 10, 2017

Hello,

I am hoping that someone may be able to help me out as I am trying to find a method to copy information from a form that I have created that has a comments section on one worksheet to a USER NAME verified comments section on another worksheet. I hope that this is self explanatory and if not please feel free to ask and I will try to explain further.

I have searched the web for the answer and cannot seem to find what I am looking for I have managed to find copy info from one sheet to another and move down as needed but that is not what I want or need. I am hoping that this can be done the way I have explained and if not I may have to just create a macro to copy info over which is not the direction I would like to head to.

Thank You ahead of time for any help
Kevin

Atroxell · Nov 10, 2017

KRKComputers said:
Hello,

I am hoping that someone may be able to help me out as I am trying to find a method to copy information from a form that I have created that has a comments section on one worksheet to a USER NAME verified comments section on another worksheet. I hope that this is self explanatory and if not please feel free to ask and I will try to explain further.

I have searched the web for the answer and cannot seem to find what I am looking for I have managed to find copy info from one sheet to another and move down as needed but that is not what I want or need. I am hoping that this can be done the way I have explained and if not I may have to just create a macro to copy info over which is not the direction I would like to head to.

Thank You ahead of time for any help
Kevin

Ok, this has been quietly sitting for an hour, so I'll take a shot at it...Let me recap for my own deeper understanding.

You have a form. The form is used to enter data into a worksheet. Part of the data in the form is a comment section, which gets placed on one worksheet-we'll call this Sheet1.

What you are looking for is a way to place the comment from Sheet1 on a second worksheet (Sheet2), aligned with a specific username, which may or may not already be on Sheet2.

Is this correct?

If so, you can get the Excel username with something like this:

Code:

Sub GetUserName_AppUser()
    Dim strUserName As String
    
    'Use the Application Object to get the Username
    strUserName = Application.UserName
    MsgBox "Current Logged In UserName is:" & strUserName
End Sub

You can adjust this as needed. Read in more detail here: https://officetricks.com/find-current-active-logged-in-user-name-from-excel-or-word/

From there, you should be able to use a find() function in your macro to find if the username is already part of Sheet2.

Does this help?

KRKComputers · Nov 10, 2017

Atroxell said:
Ok, this has been quietly sitting for an hour, so I'll take a shot at it...Let me recap for my own deeper understanding.

You have a form. The form is used to enter data into a worksheet. Part of the data in the form is a comment section, which gets placed on one worksheet-we'll call this Sheet1.

What you are looking for is a way to place the comment from Sheet1 on a second worksheet (Sheet2), aligned with a specific username, which may or may not already be on Sheet2.

Is this correct?

If so, you can get the Excel username with something like this:

Code:

Sub GetUserName_AppUser() Dim strUserName As String 'Use the Application Object to get the Username strUserName = Application.UserName MsgBox "Current Logged In UserName is:" & strUserName End Sub

You can adjust this as needed. Read in more detail here: https://officetricks.com/find-current-active-logged-in-user-name-from-excel-or-word/

From there, you should be able to use a find() function in your macro to find if the username is already part of Sheet2.

Does this help?

Atroxell,

Thanks for the update and effort it is greatly appreciated. unfortunately this is not what I am looking for and wish it was as this solution is a good one that I can add for future state but not what is needed now.

What I have is SHEET1 has user names as well as other information and I have created a form sheet that allows for vlookup and compare the name with say a phone number and so on. Also within this form at the bottom I have created a comments field or cell that when I enter info into this field (example: This guy needs to be hired with a raise) I would like to have it saved to the SHEET1 username comments field or cell or column.

Thanks for the help it is greatly appreciated
Kevin

Logit · Nov 10, 2017

.
Sounds like you want the ability to AMEND an existing record, via adding comments at the end of the row where the individual's previous personal information was entered.

Here is a mock example :

Excel 2007 32 bit

	A	B	C	D	E	F	G	H	I
1	Date	Name	Address	City	State	Zip	Email	Interview Date	Comments
2	10/1/2017	John Doe	123 Main Street	Wahoo	Illinois	24658	uhha@yahoo.com	10/1/2017
3	10/5/2017	Jane Doe	1624 South Lane	Des Moines	Iowa	74658	me@yahoo.com	10/4/2017

Sheet: Sheet1

If this is what you are after, here is some macro code and a link for project download:

Code:

'---------------------------------------------------------------------------------------
' Module    : Database Form
' DateTime  : 31/08/2005 10:55. Updatede 08-02-08
' Author    : Roy Cox
' Purpose   : Data entry form for Excel, with Search facility
' Amended   : 27/04/2013
'---------------------------------------------------------------------------------------
Dim Ws As Worksheet
Dim MyData As Range, c As Range, rFound As Range, rng As Range
Dim r As Long
Const frmMax As Long = 320
Const frmHt As Long = 210
Const frmWidth As Long = 290
Dim oCtrl As MSForms.Control


Private Sub cmbAdd_Click()
'next empty cell in column A


    Set c = MyData.Cells(MyData.Rows.Count, 1).Offset(1)


    Application.ScreenUpdating = False    'speed up, hide task
    'write userform entries to database
    With Me
        c.Value = .TextBox1.Value
        c.Offset(0, 1).Value = .TextBox2.Value
        c.Offset(0, 2).Value = .TextBox3.Value
        c.Offset(0, 3).Value = .TextBox4.Value
        If Me.optYes Then
            c.Offset(0, 4).Value = "Yes"
        ElseIf .optNo Then
            c.Offset(0, 4).Value = "No"
        End If
        'clear the form
        ClearControls
        'resize database
        Set MyData = c.CurrentRegion
        Me.ScrollBar1.Max = MyData.Rows.Count
    End With
    Application.ScreenUpdating = True
End Sub


Private Sub cmbDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("This will delete the selected record. Continue?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
    Case vbYes
       If c Is Nothing Then Set c = Ws.Cells(r, 1)
        c.EntireRow.Delete    'remove entry by deleting row
        Set MyData = Ws.Range("a8").CurrentRegion   'database
        'restore form settings
        With Me
            .cmbAmend.Enabled = False    'prevent accidental use
            .cmbDelete.Enabled = False    'prevent accidental use
            .cmbAdd.Enabled = True    'restore use
            .ScrollBar1.Max = MyData.Rows.Count
            'clear form
            ClearControls
        End With


    Case vbNo
        Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub


Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim f As Integer


'    imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator
    strFind = Me.TextBox1.Value    'what to look for


    With MyData
    .AutoFilter
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it


            With Me    'load entry to form
                .TextBox2.Value = c.Offset(0, 1).Value
                .TextBox3.Value = c.Offset(0, 2).Value
                .TextBox4.Value = c.Offset(0, 3).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                .cmbAdd.Enabled = False      'don't want to duplicate record
                If c.Offset(0, 4).Value = "Yes" Then .optYes = True
                If c.Offset(0, 4).Value = "No" Then .optYes = True
                r = c.Row
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")


                Case vbOK
                    FindAll
                Case vbCancel
                    'do nothing
                End Select
                Me.Height = frmMax


            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With


End Sub


Private Sub cmbAmend_Click()
    Application.ScreenUpdating = False
    If r <= 0 Then Exit Sub


    Set c = Ws.Cells(r, 1)
    c.Value = Me.TextBox1.Value          ' write amendments to database
    c.Offset(0, 1).Value = Me.TextBox2.Value
    c.Offset(0, 2).Value = Me.TextBox3.Value
    c.Offset(0, 3).Value = Me.TextBox4.Value
    If Me.optYes Then
        c.Offset(0, 4).Value = "Yes"
    ElseIf Me.optNo Then
        c.Offset(0, 4).Value = "No"
    End If
    'restore Form
    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        ClearControls
        .Height = frmHt
    End With
    If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub
Sub FindAll()
    Dim wesTemp As Worksheet
    Dim strFind As String    'what to find


    strFind = Me.TextBox1.Value


    If Not Ws.AutoFilterMode Then MyData.AutoFilter


    MyData.AutoFilter Field:=1, Criteria1:=strFind


    Me.ListBox1.Clear
    For Each c In MyData.Columns(1).SpecialCells(xlCellTypeVisible)
        With ListBox1
            .AddItem c.Value
            .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = c.Row
        End With
    Next c


End Sub
Private Sub cmbLast_Click()
    Dim LastCl As Range


    With MyData
        Set LastCl = .Cells(.Rows.Count, 1)
    End With


    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = LastCl.Value
        .TextBox2.Value = LastCl.Offset(0, 1).Value
        .TextBox3.Value = LastCl.Offset(0, 2).Value
        .TextBox4.Value = LastCl.Offset(0, 3).Value
         If LastCl.Offset(0, 4).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With
End Sub




Private Sub cmnbFirst_Click()
    Dim FirstCl As Range


    'first data Entry
    Set FirstCl = MyData.Cells(2, 1)


    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = FirstCl.Value
        .TextBox2.Value = FirstCl.Offset(0, 1).Value
        .TextBox3.Value = FirstCl.Offset(0, 2).Value
        .TextBox4.Value = FirstCl.Offset(0, 3).Value
        If FirstCl.Offset(0, 4).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With
End Sub


Private Sub ListBox1_Click()
Set c = Nothing
    With Me.ListBox1


        If .ListIndex = -1 Then    'not selected
            MsgBox " No selection made"
        ElseIf .ListIndex >= 1 Then    'User has selected
            r = Val(.List(.ListIndex, .ColumnCount - 1))
        End If
    End With


    With Me
        .TextBox1.Value = .ListBox1.List(.ListBox1.ListIndex, 0)
        .TextBox2.Value = .ListBox1.List(.ListBox1.ListIndex, 1)
        .TextBox3.Value = .ListBox1.List(.ListBox1.ListIndex, 2)
        .TextBox4.Value = .ListBox1.List(.ListBox1.ListIndex, 4)
         r = .ListBox1.List(.ListBox1.ListIndex, 5)
        .cmbAmend.Enabled = True      'allow amendment or
        .cmbDelete.Enabled = True     'allow record deletion
        .cmbAdd.Enabled = False       'don't want duplicate
        If ListBox1.List(.ListBox1.ListIndex, 4) = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With


End Sub


Private Sub ScrollBar1_Change()
Dim Rw As Long
Rw = Me.ScrollBar1.Value
With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = MyData.Cells(Rw, 1).Value
        .TextBox2.Value = MyData.Cells(Rw, 2).Value
        .TextBox3.Value = MyData.Cells(Rw, 3).Value
        .TextBox4.Value = MyData.Cells(Rw, 4).Value
        If MyData.Cells(Rw, 5).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With


End Sub


Private Sub UserForm_Initialize()
'change sheet name and Range here
    Set Ws = Sheet1
    Set MyData = Ws.Range("a8").CurrentRegion   'database
    With Me
        .Caption = "Database Example"    'userform caption
        .Height = frmHt
        .Width = frmWidth
        .ScrollBar1.Max = MyData.Rows.Count
        .ScrollBar1.Min = 2
    End With
End Sub


Sub ClearControls()
    With Me
        For Each oCtrl In .Controls
            Select Case TypeName(oCtrl)
            Case "TextBox": oCtrl.Value = Empty
            Case "OptionButton": oCtrl.Value = False
            End Select
        Next oCtrl
    End With
End Sub

Download link:

https://www.amazon.com/clouddrive/share/v7h3aYke2r29ohVohlDiPqVQKiDH8wbvyz8Ffhj3dxc

KRKComputers · Nov 12, 2017

Logit said:

.
Sounds like you want the ability to AMEND an existing record, via adding comments at the end of the row where the individual's previous personal information was entered.

Here is a mock example :

Excel 2007 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]	Date	Name	Address	City	State	Zip	Email	Interview Date	Comments
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]	10/1/2017	John Doe	123 Main Street	Wahoo	Illinois	24658	uhha@yahoo.com	10/1/2017
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]	10/5/2017	Jane Doe	1624 South Lane	Des Moines	Iowa	74658	me@yahoo.com	10/4/2017

Sheet: Sheet1

If this is what you are after, here is some macro code and a link for project download:

Code:

'---------------------------------------------------------------------------------------
' Module    : Database Form
' DateTime  : 31/08/2005 10:55. Updatede 08-02-08
' Author    : Roy Cox
' Purpose   : Data entry form for Excel, with Search facility
' Amended   : 27/04/2013
'---------------------------------------------------------------------------------------
Dim Ws As Worksheet
Dim MyData As Range, c As Range, rFound As Range, rng As Range
Dim r As Long
Const frmMax As Long = 320
Const frmHt As Long = 210
Const frmWidth As Long = 290
Dim oCtrl As MSForms.Control


Private Sub cmbAdd_Click()
'next empty cell in column A


    Set c = MyData.Cells(MyData.Rows.Count, 1).Offset(1)


    Application.ScreenUpdating = False    'speed up, hide task
    'write userform entries to database
    With Me
        c.Value = .TextBox1.Value
        c.Offset(0, 1).Value = .TextBox2.Value
        c.Offset(0, 2).Value = .TextBox3.Value
        c.Offset(0, 3).Value = .TextBox4.Value
        If Me.optYes Then
            c.Offset(0, 4).Value = "Yes"
        ElseIf .optNo Then
            c.Offset(0, 4).Value = "No"
        End If
        'clear the form
        ClearControls
        'resize database
        Set MyData = c.CurrentRegion
        Me.ScrollBar1.Max = MyData.Rows.Count
    End With
    Application.ScreenUpdating = True
End Sub


Private Sub cmbDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("This will delete the selected record. Continue?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
    Case vbYes
       If c Is Nothing Then Set c = Ws.Cells(r, 1)
        c.EntireRow.Delete    'remove entry by deleting row
        Set MyData = Ws.Range("a8").CurrentRegion   'database
        'restore form settings
        With Me
            .cmbAmend.Enabled = False    'prevent accidental use
            .cmbDelete.Enabled = False    'prevent accidental use
            .cmbAdd.Enabled = True    'restore use
            .ScrollBar1.Max = MyData.Rows.Count
            'clear form
            ClearControls
        End With


    Case vbNo
        Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub


Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim f As Integer


'    imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator
    strFind = Me.TextBox1.Value    'what to look for


    With MyData
    .AutoFilter
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it


            With Me    'load entry to form
                .TextBox2.Value = c.Offset(0, 1).Value
                .TextBox3.Value = c.Offset(0, 2).Value
                .TextBox4.Value = c.Offset(0, 3).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                .cmbAdd.Enabled = False      'don't want to duplicate record
                If c.Offset(0, 4).Value = "Yes" Then .optYes = True
                If c.Offset(0, 4).Value = "No" Then .optYes = True
                r = c.Row
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")


                Case vbOK
                    FindAll
                Case vbCancel
                    'do nothing
                End Select
                Me.Height = frmMax


            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With


End Sub


Private Sub cmbAmend_Click()
    Application.ScreenUpdating = False
    If r <= 0 Then Exit Sub


    Set c = Ws.Cells(r, 1)
    c.Value = Me.TextBox1.Value          ' write amendments to database
    c.Offset(0, 1).Value = Me.TextBox2.Value
    c.Offset(0, 2).Value = Me.TextBox3.Value
    c.Offset(0, 3).Value = Me.TextBox4.Value
    If Me.optYes Then
        c.Offset(0, 4).Value = "Yes"
    ElseIf Me.optNo Then
        c.Offset(0, 4).Value = "No"
    End If
    'restore Form
    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        ClearControls
        .Height = frmHt
    End With
    If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub
Sub FindAll()
    Dim wesTemp As Worksheet
    Dim strFind As String    'what to find


    strFind = Me.TextBox1.Value


    If Not Ws.AutoFilterMode Then MyData.AutoFilter


    MyData.AutoFilter Field:=1, Criteria1:=strFind


    Me.ListBox1.Clear
    For Each c In MyData.Columns(1).SpecialCells(xlCellTypeVisible)
        With ListBox1
            .AddItem c.Value
            .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = c.Row
        End With
    Next c


End Sub
Private Sub cmbLast_Click()
    Dim LastCl As Range


    With MyData
        Set LastCl = .Cells(.Rows.Count, 1)
    End With


    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = LastCl.Value
        .TextBox2.Value = LastCl.Offset(0, 1).Value
        .TextBox3.Value = LastCl.Offset(0, 2).Value
        .TextBox4.Value = LastCl.Offset(0, 3).Value
         If LastCl.Offset(0, 4).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With
End Sub




Private Sub cmnbFirst_Click()
    Dim FirstCl As Range


    'first data Entry
    Set FirstCl = MyData.Cells(2, 1)


    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = FirstCl.Value
        .TextBox2.Value = FirstCl.Offset(0, 1).Value
        .TextBox3.Value = FirstCl.Offset(0, 2).Value
        .TextBox4.Value = FirstCl.Offset(0, 3).Value
        If FirstCl.Offset(0, 4).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With
End Sub


Private Sub ListBox1_Click()
Set c = Nothing
    With Me.ListBox1


        If .ListIndex = -1 Then    'not selected
            MsgBox " No selection made"
        ElseIf .ListIndex >= 1 Then    'User has selected
            r = Val(.List(.ListIndex, .ColumnCount - 1))
        End If
    End With


    With Me
        .TextBox1.Value = .ListBox1.List(.ListBox1.ListIndex, 0)
        .TextBox2.Value = .ListBox1.List(.ListBox1.ListIndex, 1)
        .TextBox3.Value = .ListBox1.List(.ListBox1.ListIndex, 2)
        .TextBox4.Value = .ListBox1.List(.ListBox1.ListIndex, 4)
         r = .ListBox1.List(.ListBox1.ListIndex, 5)
        .cmbAmend.Enabled = True      'allow amendment or
        .cmbDelete.Enabled = True     'allow record deletion
        .cmbAdd.Enabled = False       'don't want duplicate
        If ListBox1.List(.ListBox1.ListIndex, 4) = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With


End Sub


Private Sub ScrollBar1_Change()
Dim Rw As Long
Rw = Me.ScrollBar1.Value
With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = MyData.Cells(Rw, 1).Value
        .TextBox2.Value = MyData.Cells(Rw, 2).Value
        .TextBox3.Value = MyData.Cells(Rw, 3).Value
        .TextBox4.Value = MyData.Cells(Rw, 4).Value
        If MyData.Cells(Rw, 5).Value = "Yes" Then
            .optYes = True
        Else: .optNo = True
        End If
    End With


End Sub


Private Sub UserForm_Initialize()
'change sheet name and Range here
    Set Ws = Sheet1
    Set MyData = Ws.Range("a8").CurrentRegion   'database
    With Me
        .Caption = "Database Example"    'userform caption
        .Height = frmHt
        .Width = frmWidth
        .ScrollBar1.Max = MyData.Rows.Count
        .ScrollBar1.Min = 2
    End With
End Sub


Sub ClearControls()
    With Me
        For Each oCtrl In .Controls
            Select Case TypeName(oCtrl)
            Case "TextBox": oCtrl.Value = Empty
            Case "OptionButton": oCtrl.Value = False
            End Select
        Next oCtrl
    End With
End Sub

Download link:

https://www.amazon.com/clouddrive/share/v7h3aYke2r29ohVohlDiPqVQKiDH8wbvyz8Ffhj3dxc

@ Logit

Thanks for the response and my apologies for not getting back sooner.

The way that you have the example form showing on the screen is correct as far as the form style goes and I will hopefully get this correct in the way that I am attempting to explain what I am doing as I am 100 percent certain it is the way I am stating what I am trying to do.

The form that you have above is the example and you have "SHEET" and SHEET2" within the fields on "SHEET2" you have a "COMMENT" field and what I am trying to do is if you enter information into that "COMMENT" field and you have the same field on "SHEET" I want it to copy that over based on confirmed criteria to a specific cell or row or column. This specific cell,row, or column could be any of the following "USER NAME" or an "BADGE ID" number for example.

My only concern is that the comments go under the same USER NAME "COMMENTS" field that they are placed within. I do not want sally jones to get a comment that was meant for bobs plumbing.

Thanks again to all for the help it is greatly appreciated.
Kevin

Logit · Nov 12, 2017

.
Ok .. the project example linked in my last post will do what you are seeking. Albeit, you will need to edit it so it completely adheres to the columns/data you have in your project.

The ability of the download project to "EDIT" a listing is specifically what will accomplish your need. The form displays the individual record you intend to ammend, then you can edit any field
in that single record.

I encourage you to study the macro code ... play around with it ... see what happens when you change the code. It's the fastest way I know of 'learning hands on' how to manipulate existing code for your
purposes. When you run into a problem, post your question for the FORUM members to answer/assist.

Cheers.

VBA Question or otherwise

KRKComputers

New Member

Excel Facts

Atroxell

Active Member

KRKComputers

New Member

Logit

Well-known Member

KRKComputers

New Member

Logit

Well-known Member

Similar threads

Forum statistics

Share this page

VBA Question or otherwise

KRKComputers

New Member

Excel Facts

Atroxell

Active Member

KRKComputers

New Member

Logit

Well-known Member

KRKComputers

New Member

Logit

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock