using macro to reorder and combine

[kellem80]

I'd like to create a macro that will take the information from the first two columns reformat and create the third column as shown formatted. Any help would be appreciated.
<TABLE style="WIDTH: 180pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=240 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=72>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=68>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=100>Number</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=17 x:num="40425.384027777778">9/4/10 9:13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>Bob Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>100904-0913BS</TD></TR></TBODY></TABLE>

 

[Joe4]

Like I said, I don't use User Forms attached to spreadsheets a lot anymore, but when I did, they all involved the use of VBA to transfer the entries made on the Form to the Excel sheet.

So how exactly does the information entered in the Form populate the Spreadsheet?

 

[Joe4]

Maybe you mean that the Excel sheet itself IS the entry Form?
(It can be a little confusing, because there are such things as Forms in Excel that you can create, but you have to use VBA to link the entries back to particular cells on your Excel sheet).

If that is the case, you can use what is called a Worksheet_Change event procedure which is automatically fired whenever a certain cell is manually updated. I have some code for that. Simply right click on the sheet tab name, select View Code, and paste the following code into the resulting blank window:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'   Check to see if change is made in cell B2
    If Target.Address = "$B$2" And Target.Count = 1 Then
'   Disable worksheet_change macro from calling itself
        Application.EnableEvents = False
'   Update column to the right
        Target.Offset(0, 1) = Format(Now(), "yyyymmdd-hhmm") & Left(Target.Value, 1) & Mid(Target.Value, InStr(Target.Value, " ") + 1, 1)
'   Turn events back on
        Application.EnableEvents = False
    End If
            
End Sub

I tried to document the code for you. It is really just a variation of the other VBA code. What it does is whenever an entry is made in cell B2, it will automatically update cell C2 with the current date and time, and the initials of the entry in B2. However, it will be entered as a "hard-coded" value, and not a formula.

Is that kind of what you are looking for?

 

[kellem80]

EXACTLY!
I am going to try that now.
I am setting up the basic form first in SS then converting to Form form after all fields are set and agreed upon. Somebody else will be heading up the conversion, hopefully or i will be back on the board for some tips!
Thanks!
km

 

[Joe4]

Your welcome.
I'm glad we were able to get it all sorted out.