Excel/user form

Advertisement


Question
QUESTION: Tom

you helped me awhile back on this formula for a user form to be able to view the data on our customer page in certain cells and its works fine....we needed to add another form to show data from a different group of cells, i have tried changing the columns but duplicated the formula, the problem is once i do that and it shows fine but the information isnt there, then when i click on the original one it shows the information from the newest user form, so we changed it back and the original one works fine.  However i still cant get both to work

here is the code of the first one



Private 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("Customer")
Set r = sh.Range("As3:As8")
k = 1
For i = 0 To 3
Set r1 = r.Offset(0, i)
For Each cell In r1
ClientInfo.Controls("Label" & k).Caption = cell.Text
k = k + 1
Next
Next
End Sub

and here is the code of the second one we just added


Private 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("Customer")
Set r = sh.Range("As12:As17")
k = 1
For i = 0 To 3
Set r1 = r.Offset(0, i)
For Each cell In r1
ClientInfo.Controls("Label" & k).Caption = cell.Text
k = k + 1
Next
Next
End Sub

i have changed the UserForm_Initialize to 2 but i am not sure if this can be done on the same page or not or what has to be done....

any assistance would be greatly appreciated Tom

Thanks

ANSWER: Anthony,

You should not be able to see both versions of the code in the same module.  Each userform has its own Initialize event.  So if you are showing two different userforms, one userform should have the first initialize event you show (in its code module) and the other userform should have the second initialize event that you show.  The name remains Userform_Initialize but they are in the respective class modules for the appropriate userform.  


That said, here is another problem (and if you have the code in separate userforms it may be the only problem)

both sets of code use

ClientInfo.Controls("Label" & k).Caption = cell.Text

I assume that is the name of the userform.  So only the clientinfo userform should have code like that.  If the other form is named Clientinfo2 (as an example - I have no way of knowing), then that line of code in the copy of the initialize event in that forms code module would change to

ClientInfo2.Controls("Label" & k).Caption = cell.Text

--
Regards,
Tom Ogilvy


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

QUESTION: thanks for your reply, i sent you another email right after this one telling i found that issue, the second is named appliances - i sent the code i used as well.  that fix the issue of the information for the appliances showing up in clientinfo form, both now show up fine and client info shows the right data, however i cant get the appliances to show that data, i have tried placing the data in two different manners, in cells aw3 : aw8 place the data in the two columns, aw3:ax8 but nothing shows up.

as for the Initialize if i leave it as Userform_Initialize it is showing me an error to the macro that code that opens the form.  if i place a 2 after it it opens fine....


Tony

ANSWER: Tony,

Assume the error is duplicate name.  

It sounds like you have the code in the wrong userform module (both in the code module for clientinfo).  It you think not, then send the workbook to me at twogilvy@msn.com



--
Regards,
Tom Ogilvy


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

QUESTION: ok tom

sent the demo program

Thanks

Tony

Answer
Tony,

I found the problem and sent it back.  You were looping through more cells than there were labels to hold the data so that caused the error.  When you change the name to Userform_Initialize2, that is not the name of an event, so that code doesn't run.  It then doesn't raise an error but it also doesn't populate the labels.

See my email.

--
Regards,
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

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.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.