PDA

View Full Version : Userform macro to update data in specific range



Silver
07-04-2016, 12:12 PM
Hi,

I have a userform with 11 textboxes and 1 command button,

Data entered in the first 10 textboxes are updated in specific columns on sheet1 from row 2 onwards.

Below is the code


Const F = "dd-mm-yy hh:mm"


Private Sub CommandButton1_Click()
N% = Val(TextBox11.Text)
If N < 1 Then TextBox10.SetFocus: Beep: Exit Sub
V = [{8,11,12,13,16,19,20,21,22,23}]
r& = Sheet2.Cells(Sheet2.Rows.Count, V(1)).End(xlUp)(2).Row
For c% = 1 To UBound(V)
Sheet2.Cells(r, V(c)).Resize(N).Value = Me.Controls("TextBox" & c).Text
Me.Controls("TextBox" & c).Text = ""
Next
UserForm_Initialize
End Sub


Private Sub TextBox2_Enter()
TextBox2.Text = Format$(Now, F)
End Sub


Private Sub UserForm_Initialize()
TextBox3.Text = Format$(Now, F)
TextBox11.Text = "1"
End Sub
Private Sub TextBox4_Enter()
TextBox4.Text = Format$(Now, F)
End Sub


EDIT -

Since range D2:M19 will be used for manual updation of data, I would like the macro to update the data in range D20:M30.

Kindly assist.

Have attached sample sheet.

SamT
07-04-2016, 04:21 PM
Not with that code. Your TextBox names are tied to the column Numbers. That UserForm was designed for a specific Worksheet layout. Since you changed the Worksheet layout, you must redesign the UserForm.

The easiest way, not the best, but good enough, is to add the Column number to each TextBox'es Tag property, but only the Textboxes with values that go on the worksheet.

Then this code will work


Private Sub CommandButton1_Click()
Dim Ctrl As Object
N% = Val(TextBox11.Text)
If N < 1 Then TextBox10.SetFocus: Beep: Exit Sub

r& = Sheet2.Cells(Sheet2.Rows.Count, V(1)).End(xlUp)(2).Row
If r < 20 then r = 20

For Eanch Cgtrl in Me.Controls
If Ctrl.Tag > 0 then
Sheet2.Cells(r, Ctrl.Tag).Resize(N).Value = Ctrl.Text 'Sheet2 is an Object Name.See below
Ctrl.Text = ""
End If
Next
UserForm_Initialize
End Sub

All Sheets have two names, The "Tab" name and the Object Name. In the VBA editor, use the "View" menu to show the Object Explorer Window and the Properties Window. In the Object Explorer Window you will see each sheet with names like

Sheet1(Sheet1)
Where the name in the Parentheses is the "Tab" name and the other is the Object Name.

In the Properties Window, you will see them listed in the opposite manner

(Name) | Sheet1
*
*
Name | Sheet1

Where the Object.Name Property is distinguished by the Parentheses and the "Tab" Name is not. The Properties Window is where you can edit the Object Name, (and the Tab name, too.)

In Code the two Names are different in that the Tab Names is used like

Sheets("Sheet1").Range("A1")
And the Object Name is used like it is in the code above

Sheet1.Range("A1")

The advantage of using Object Names, (technically,they are "CodeNames",) is that if someone changes the Tab name it won't break the code.

Dim mySheetObjectName As String
mySheetObjectName = Sheets("Sheet1").CodeName

Silver
07-04-2016, 10:41 PM
SamT,

You are awesome... :bow: :bow: :bow:

I learnt quite a number of things.

Didn't know that I could tag textbox to a column.

You broke down the explanation of Tab and Object Names in the most simplest manner.

I should take coding classes from you... MANY THANKS

SamT
07-05-2016, 08:07 AM
You can use the Tag Property for anything. It's the generic "Note" property.

I don't code the Form to Sheet interface like you did, I use a Ctrl.Name to Range Name style and put "IO" in the Tags of Ctrls that have inputs to the sheets. This eliminates 'Magic Numbers' and allows the rearrangement of columns without breaking the code.

IMO,developing a naming convention is very important. Say, for instance, I have three sheets, Employees, Vendors, and Clients. Obviously some header names will be common to all three, ie Last Name.

The Headers would all be Last Name, and the Range names; empLast_Name, venLast_Name and cliLast_Name. On the common UserForm, the Last Name control would be named cbxLast_Name. "cbx" for ComboBox.

Now, assuming there is a Control, lbxPersonType, and all data input Control's Tag's = "IO",

NamePrefix = LCase(Left(lbxPersonType.value, 3))

For each Ctrl in Me.Controls
If Ctrl.Tag = "IO" And Ctrl.Value <> "" Then
Range(NamePrefix & Mid(Ctrl.Name, 4)) = Ctrl.Value
Next Ctrl

Now if I put Person Type specific Controls in Frames and show/hide Frames depending on the value of lbxPersonType, I can use one Form and those four lines of code for all such data input.