Excel/Data to UserForm



Greetings! Hope all is good.  I have a question and hope you can assist.

I have a worksheet named CustomerData on that i have Cells h5:j10 filled with data
i want to be able to place a macro on another worksheet named Customer and have a user form simply pop up to show that data as it appears on that CustomerData sheet, here is whats on the data:

h5   - text
h6   - social security # with -'s
h7   - date of birth formated and appears as  4 March 1955
h8   - text
h9   - text
h10 - phone # formated and appears as  (210) 345-3322
h11 - text email address

Question.  I have created a UserForm but for the life of me can not figure out how to have the UserForm show simply in text as it appears on worksheet.  I figured using labels would work but have tried a few different codes but am stalled. And the information may change depending on what client they choose, example once the select a client that information appears on CustomerData sheet

Thanks ahead of Time Tom!!!


ANSWER: Anthony,

if the activecell displays the value  4 March 1955  and contains a true excel date value then

activecell.value  will be  20152   
this is the date serial number.  It is what is actually stored for the cell value.  It is the number of days since 1900 which is how dates are stored.  

format the cell to General to see for yourself.  (now format it back to the way it was)

If you want your control to have the displayed date use the text property

Label1.Caption =  Activecell.Value
should display 20152

Label1.Caption = ActiveCell.Text
should display 4 March 1955

Label1.Caption = Format(cell.Value,"d mmmm yyyy")

but that would require you specifying the required format for each assignment.  Using the text property, you don't have to worry about that.

So that should give you the information you need.

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: ok that makes sense, but i still havent a clue what or how i put the codes on the useform, do i select a label and then go to to the view code for the label and input  that specific code you stated above? and the once i have the userform like i want it simply have a macro call it up? for example as below

for the Date to display 3 Mar 66
Private Sub Label1_Click()
Label1.Caption = Format(cell.Value,"d mm yy")
End Sub

for the Social Security its formatted as special and enters the -'s -  will the one below still show the ssn as 444-44-4444 or will it show 44444444?

Private Sub Label2_Click()
Label1.Caption = ActiveCell.Text
End Sub


ANSWER: Anthony,

The userform has an initialize event.  You would put code to make the assignment there

Sub Userform_Initialize()
Dim sh as worksheet, r as range, r1 as Range
Dim k as long, i as long, cell as range
set sh = worksheets("CustomerData"
set r = sh.Range("H5:H10")
k = 1
for i = 0 to 2
set r1 = r.offset(0, i)
for each cell in r1
  userform1.Controls("Label" & k).Value = cell.Text
  k = k + 1
End Sub

so I have assumed you have 18 labels named Label1 to Label18  and you want to process H5:J10 one column at a time (6 cells in each column).  

Labels1 to 6 will have cells H5:H10 in sequence
Label7 to Label12 will have cells I5:I10 in sequence
Label13 to Label18 will have cells J5:J10 in sequence

each label would show the data as it is displayed on the sheet.

>for the Social Security its formatted as special and enters the -'s -  will the one below still show the ssn as 444-44-4444 or will it show 44444444?

I don't really understand that statement, but if the cell show  444-44-4444 then the label will show 444-44-4444

the initialize event (named userform_initialize regardless of the name of the userform) will fire whenever the userform is loaded.  

You would need code that has a line like like this  (assume the name of the userform is userform1)


to show the userform.

in the userform itself you would have a commandbutton to close out the userform

Private Sub commandbutton1_click()
Unload me   ' me refers to the userform
End Sub

that code is in the useform module.

You would create no links to cells in the properties of the controls.  You would not use any events associated with the controls.  The initialize procedure would do all the work loading the form.  

So I have stated the assumptions to illustrate an approach.  You would have to adapt the approach to suit your specific needs.

Note that in your original post you defined values for rows 5 to 11 (H5:H11), but in your description you said you wanted to get data from H5:J10  

Tom Ogilvy

---------- FOLLOW-UP ----------


Thanks for the response.  All that pretty much makes sense, the ONLY that is that I can not find where "userform has an initialize event" is to place the code, i have looked in properties, but cant find something letting me select it, double clicked on label and commandbutton1....nada

i know its a stupid question but where do i find it to place the code you sent me



in the userform in the Visual Basic Editor, double click on the userform.

In the resulting module, at the top, in the left dropdown, select your userform

(possibly userform will already be showing as double clicking will by default put in the click event for the userform).

Now go to the right dropdown at the top and select Initialize.

this will put in

Private Userform_Initialize()

End sub

that is the initialize event.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.