Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Maintain original data format during update

Posted on 2011-04-23
Medium Priority
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
  • 2
  • 2
LVL 30

Accepted Solution

SiddharthRout earned 2000 total points
ID: 35453689
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

ID: 35453738
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

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

Which cell are you referring to?


Author Closing Comment

ID: 35752945
Thanks Sid for the help

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 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