Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Populating VBA userform and editing/updating data

  1. #1
    New Member
    Join Date
    May 2009
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Populating VBA userform and editing/updating data

    Hi,

    I have the following code to put data from a VBA userform into Excel

    Dim Sh As Worksheet
    Dim Rng As Range
    Set Sh = ActiveSheet
    Set Rng = Sh.Range("A65536").End(xlUp).Offset(1, 0)
    With Rng
    .Cells(1, 1) = Surname.Text
    .Cells(1, 2) = forename.Text
    .Cells(1, 3) = datein.Text
    .Cells(1, 4) = origin.Text
    .Cells(1, 5) = Addressee.Value
    .Cells(1, 6) = usual.Value
    .Cells(1, 7) = dateto.Text
    .Cells(1, 8) = permission.Value
    .Cells(1, 9) = dateseen.Text
    .Cells(1, 10) = requestview.Value
    .Cells(1, 11) = Invoice.Value
    .Cells(1, 12) = notes.Text
    .Cells(1, 13) = datecompleted.Text
    .Cells(1, 14) = holdsend.Value
    .Cells(1, 15) = fee.Text
    .Cells(1, 16) = notes2.Text
    .Cells(1, 17) = dateseen.Text
    .Cells(1, 18) = invoicesent.Text
    .Cells(1, 19) = Paid.Text
    .Cells(1, 20) = Complete.Value

    End With

    What I want to do next is click on the surname on the speadsheet and call up the userform with the fields complete for that person. I would also like to be able to edit/update the form and update the information on the speadsheet accordingly.

    Any help would be greatly appreciated!

    Many thanks!

  2. #2
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,493
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Populating VBA userform and editing/updating data

    Hi,

    assuming that:
    1) you have 20 text boxes named 'TextBox1', TextBox2' ... 'TextBox20'
    2) The text box number equates to the column for the data, so TextBox1 would hold the surname, TextBox2 would hold the fore Name, TextBox3 would hold the Date In etc
    3) Double-Click the required surname will bring up the userform
    4) The Userform is named 'Userform1"

    The double-Click event:
    Code:
    Option Explicit
    
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim frmUpdate As UserForm1
    
    Cancel = True
    
    If Not (Intersect(Target, Columns("A")) Is Nothing) Then
        Set frmUpdate = New UserForm1
        Set frmUpdate.TargetSheet = ActiveSheet
        frmUpdate.CurrentRow = Target.Row
        frmUpdate.Show
        Set frmUpdate = Nothing
    End If
    End Sub
    The Userform code:
    Code:
    Option Explicit
    Public TargetSheet As Worksheet
    Public CurrentRow As Long
    Private Sub btnCancel_Click()
    Me.Hide
    End Sub
    
    Private Sub btnUpdate_Click()
    Dim ctl As Control
    Dim iColumn As Integer
    
    For Each ctl In Me.Controls
        If Left$(ctl.Name & "1234567", 7) = "TextBox" Then
            iColumn = Val(Mid$(ctl.Name, 8))
            TargetSheet.Cells(CurrentRow, iColumn).Value = ctl.Text
        End If
    Next ctl
    
    Me.Hide
    End Sub
    
    Private Sub UserForm_Activate()
    Dim ctl As Control
    Dim iColumn As Integer
    
    For Each ctl In Me.Controls
        If Left$(ctl.Name & "1234567", 7) = "TextBox" Then
            iColumn = Val(Mid$(ctl.Name, 8))
            ctl.Text = TargetSheet.Cells(CurrentRow, iColumn).Value
        End If
    Next ctl
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        ' X was clicked
        Cancel = True
        btnCancel_Click
    End If
    End Sub
    HTH

    Alan

    --------------------------------------------------------
    Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
    Alternatively to compare two worksheets try Compare Two Sheets
    --------------------------------------------------------
    There are 10 kinds of people - those who understand binary and those who don't

  3. #3
    New Member
    Join Date
    Feb 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Populating VBA userform and editing/updating data

    Hi there,

    Can you please explain a piece of your code plx:

    If Left$(ctl.Name & "1234567", 7) = "TextBox" Then iColumn = Val(Mid$(ctl.Name, 8)) ctl.Text = TargetSheet.Cells(CurrentRow, iColumn).Value

  4. #4
    Board Regular
    Join Date
    Jul 2003
    Location
    Manchester (UK)
    Posts
    4,493
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Populating VBA userform and editing/updating data

    Hi,
    This piece of code is within a "for" loop which loops thru all the controls in the userform, and implements point (2) of the explanatory text.
    It assumes that there are a number of textboxes in the userform all named "TextBox" followed by a number. This number specifies the column in which to write the data.
    If the current control name starts "TextBox" it sets variable "iColumn" to the number of the text ox ( so "TextBox15" would set iColumn" to 15).
    The Textbox value is then written to the current row, column 15.
    HTH

    Alan

    --------------------------------------------------------
    Vlookup not sufficient? Follow the link for latest version of FuzzyVLookup or RuleLookup .
    Alternatively to compare two worksheets try Compare Two Sheets
    --------------------------------------------------------
    There are 10 kinds of people - those who understand binary and those who don't

Some videos you may like

User Tag List

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
  •