Maintain original data format during update

Posted on 2011-04-23
Last Modified: 2012-05-11
Hi Experts,

I would like to request Experts help. How to prevent the TextBox1 (attached script) at Userform2 doesn’t modify  the actual data format while updating the content.  The userform 2 activated when double click at column J or K for data update. E.g. data at cell J44 and K44 got deleted when update the data via userform. Hope Experts will help me to prevent the original data and the format were distracted while update/amend the content.  

Option Explicit

Private Sub CommandButton1_Click()
    Dim MyArray() As String
    MyArray = Split(Me.TextBox1.Value, vbCrLf)
    Sheets("Daily Tracking List").Cells(Selection.Row, "J").Value = Trim(Replace(MyArray(0), "Issues:", ""))
    Sheets("Daily Tracking List").Cells(Selection.Row, "K").Value = Trim(Replace(MyArray(2), "Findings/Updates:", ""))
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Sheets("Daily Tracking List").Cells(Selection.Row, "J").ClearContents
    Sheets("Daily Tracking List").Cells(Selection.Row, "K").ClearContents
    Unload Me
End Sub

Private Sub UserForm_Initialize()
With Me.TextBox1
    .WordWrap = True
    .MultiLine = True
End With
With Sheets("Daily Tracking List")
    Me.TextBox1.Value = "Issues: " & .Cells(Selection.Row, "J") & vbCrLf & vbCrLf & _
        "Findings/Updates: " & .Cells(Selection.Row, "K")
End With
End Sub

Open in new window

Question by:Cartillo
    LVL 30

    Accepted Solution

    Try this


    Code Changed to

    Private Sub CommandButton1_Click()
        Dim Str1 As String, Str2 As String
        Dim Pos1 As Long, Pos2 As Long
        Str1 = "Issues:"
        Str2 = "Findings/Updates:"
        Pos1 = InStr(1, Me.TextBox1.Value, Str1, vbTextCompare)
        Pos2 = InStr(1, Me.TextBox1.Value, Str2, vbTextCompare)
        Sheets("Daily Tracking List").Cells(Selection.Row, "J").Value = _
        Mid(Me.TextBox1.Value, Len(Str1) + Pos1, Pos2 - (Len(Str1) + Pos1))
        Sheets("Daily Tracking List").Cells(Selection.Row, "K").Value = _
        Mid(Me.TextBox1.Value, Len(Str2) + Pos2, Len(Me.TextBox1.Value) - Pos2 - 1)
        Unload Me
    End Sub

    Open in new window


    Author Comment

    Hi Sid,

    The data got updated except for data with “Bullet Library” or “numbering library”. Most of the time the actual data was copied over from words document for compilation. Hope we can salvage this object as well while updating the data.
    LVL 30

    Expert Comment

    >>>The data got updated except for data with “Bullet Library” or “numbering library”.

    Which cell are you referring to?


    Author Closing Comment

    Thanks Sid for the help

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Excel to create SQL insert 7 28
    Search multiple lines 3 26
    VBA Overflow problem when filtering 14 25
    exporting issues in Access 2 10
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now