Populating VBA userform and editing/updating data

Vodkamuncha

New Member
Joined
May 29, 2009
Messages
12
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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
 

waiting4u

New Member
Joined
Feb 8, 2014
Messages
1
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</pre>
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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.
 

Forum statistics

Threads
1,175,656
Messages
5,898,703
Members
434,723
Latest member
Epeople

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
Top