• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I paste my updated UserForm back to the sheet?

C

Cumiskey

New Member
Hello Everyone!

I am close to losing the will to complete this task! I hope that someone can save me!

Attached is a workbook i have been working on for work and so far i have created 2 UserForms. One of them works fine and is linked to the 'Place DMD' button. The other is linked to 'Edit/Update' button.

The 'Edit Update' form is where the problem is. I can pick information from my sheet via this form using the ListBox but when I update the information and close the form the information is not changed on the sheet? What i would like is another button on the Userform that will submit the changes to the sheet.

Could someone please help! I would be forever grateful!!

Thanks

Mark
 

Attachments

  • NewGEFworkinprogress1 - V1.xlsm
    97.6 KB · Views: 4
Chihiro said:
Is "AKI DMD NO" unique index? If so you may find following thread useful.

http://chandoo.org/forum/threads/validation-help.31957/#post-189930

Basically uses Scripting.Dictionary with UID as key to store cell reference. Using that you can find and update info quickly in subsequent code.
Click to expand...

Thank you for your response, unfortunately I am no good at VBA and cannot make sense of what you supplied in the link. Would it be possible for you to provide me with the code to achieve my aim? I know its asking a lot of you and your own time but I am really desperate to have this working and will be so thankful!!

▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !
​
 
But you didn't answer my question. Is "AKI DMD NO" unique list? Or is there any other column that has unique value for each line?
 
Chihiro said:
But you didn't answer my question. Is "AKI DMD NO" unique list? Or is there any other column that has unique value for each line?
Click to expand...

Sorry, yes it is the unique list/Index. The situation I have is many lines can have the same identifier.
 
I don't know how Dictionary could play in this case though.
No dictionary.
1) Change Initialize event code to
Code: 
Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ColumnCount = 3
        .List = Sheets("dmds").[b19].CurrentRegion.Value
    End With
End Sub
2) change cmdUpdate_Click event code to
Code: 
Private Sub cmdUpdate_Click()
    With Me.ListBox1
        If .ListIndex <> -1 Then
            .List(.ListIndex, 0) = TextBox1.Value
            .List(.ListIndex, 1) = TextBox2.Value
            .List(.ListIndex, 2) = TextBox3.Value
            Sheets("dmds").[b19].CurrentRegion.Resize(, 3).Value = .List
        End If
    End With
End Sub
 

Attachments

  • NewGEFworkinprogress1 - V1 with code.xlsm
    93.6 KB · Views: 7
jindon said:
I don't know how Dictionary could play in this case though.
No dictionary.
1) Change Initialize event code to
Code: 
Private Sub UserForm_Initialize()
    With Me.ListBox1
        .ColumnCount = 3
        .List = Sheets("dmds").[b19].CurrentRegion.Value
    End With
End Sub
2) change cmdUpdate_Click event code to
Code: 
Private Sub cmdUpdate_Click()
    With Me.ListBox1
        If .ListIndex <> -1 Then
            .List(.ListIndex, 0) = TextBox1.Value
            .List(.ListIndex, 1) = TextBox2.Value
            .List(.ListIndex, 2) = TextBox3.Value
            Sheets("dmds").[b19].CurrentRegion.Resize(, 3).Value = .List
        End If
    End With
End Sub
Click to expand...

Firstly thank you very much!! If i was to change which columns were loaded into the list box to be edited, would it be as simple as changing b19 to say l19 for example?
 
You only have 3 textbox though...
The logic is very simple.
Load all the data in ListBox, hide unneeded columns, update the ListBox then bring the data back to the range.
Code: 
Private Sub cmdEdit_Click()
    If ListBox1.ListIndex <> -1 Then
        With ListBox1
            TextBox1.Value = .List(.ListIndex, 10)
            TextBox2.Value = .List(.ListIndex, 11)
            TextBox3.Value = .List(.ListIndex, 12)
        End With
    End If
End Sub

Private Sub cmdUpdate_Click()
    Dim x
    With Me.ListBox1
        If .ListIndex <> -1 Then
            .List(.ListIndex, 10) = TextBox1.Value
            .List(.ListIndex, 11) = TextBox2.Value
            .List(.ListIndex, 12) = TextBox3.Value
            Sheets("dmds").[b19].CurrentRegion.Resize(, .ColumnCount).Value = .List
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    With Me.ListBox1
        .List = Sheets("dmds").[b19].CurrentRegion.Value
        .ColumnCount = Sheets("dmds").[b19].CurrentRegion.Columns.Count
        .ColumnWidths = "0;0;0;0;0;0;0;0;0;0;40;120;50;50"
    End With
End Sub
 
I don't know how Dictionary could play in this case though.
Click to expand...

Ah, I figured that with 900+ lines reserved for data entry, it would be easier to load dictionary with unique list as key and cell as item.

Using it to verify key field is unique before submission. And use it to search address to update upon edit.
 
You must log in or register to reply here.
Back
Top