UserForm Retreval, Amend and Return

**Endorphin** · 07-17-2008, 11:24 AM

Hi all,

Is it possible for a userform to retrieve information from a worksheet, the user to adjust it and then the userform to overwrite the original information?

Any help really appreciated..

Endorphin

**Leith Ross** · 07-17-2008, 12:14 PM

Hello Endorphin,

Here is a simple example. This uses a text box to accept the user's entry, and two command buttons: one to read the data into the text box and the other to write the data back to the cell. You would probably need to added some validation and formatting to this code based on your needs.

Sub CommandButton1_Click()
 'READ THE DATA
  TextBox1.Text = ActiveSheet.Range("A1")
End Sub

Sub CommandButton2_Click()
 'WRITE THE DATA
  ActiveSheet.Range("A1") = TextBox1.Value
End Sub

Sincerely,
Leith Ross

**Endorphin** · 07-17-2008, 12:25 PM

Thank you, atleast I know it can be done. I am only just learning VBA and if I get stuck, could I ask for help on this?

Neil

**Leith Ross** · 07-17-2008, 12:27 PM

Hello Neil,

Of course, anytime.

Sincerely,
Leith Ross

**Endorphin** · 07-17-2008, 02:42 PM

Hi Leith / Anyone

Ok, I am now stuck, I have created the document but unable to retrieve the data into the Userform.

The signal IDs will always be the same.

1. How do I get the information into the form once I have selected the Signal ID.
2. If one of the lamp has been changed, I would like to press the “Archive lamp button” and have the newest date move to the old date leaving a space to put the new new date in.
3. Is there anyway to force excel into opening on the Aspect Data Entry page whatever page it has been saved on.
Solved this one, see below

Option Explicit
Sub Auto_Open()
Application.Goto ThisWorkbook.Worksheets("Aspect Data Entry").Range("a1"), _
Scroll:=True
End Sub

Again, any help is appreciated.

Neil

**royUK** · 07-18-2008, 04:17 AM

use the change event of the combobox

Private Sub cboPart_Change()
    Dim cl     As Range
    Dim rng    As Range
    Dim sfind  As String
    sfind = Me.cboPart.Text
    With wksPartsData
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    With rng
        Set cl = .Find(sfind, LookIn:=xlValues)
        If Not cl Is Nothing Then
            With Me
                .ROV = cl.Offset(0, 4).Value
                .YOV = cl.Offset(0, 5).Value
                'etc
            End With
        End If
    End With
End Sub

See attached, I've also simplified the combobox loading

You also mention using Auto_Open, this code should now be in the WorkBook_Open event.