UserForm Query

UserForm Query

I have a User Form which is used to collect monthly cost information for a 
five year period for a database.   The start month and year of the five year 
period will vary from record to record.

Intially the user enters the start month and start year into separate Text 
Boxes.   From this information I have created an Array, strMonthLabels(60), 
which contains each month and year for the five year period; i.e. "Jan 2009", 
"Feb 2009", etc.

Each of the Text Boxes to capture the month cost information has an adjacent 
Label to signify the month and year so that the user can enter the data 
correctly.   These Labels are named lblMonth1 through to lblMonth60.

I want to be able to use a For-Next Loop to change the Captions on each of 
the Labels.   The following indicates my intententions but does not work.

    For i = 1 To 60
        lblMonth(i).Caption = strMonthLabels(i)
    Next

An alternative I have tried unsuccessfully is to create a Label variable
    
    Dim objLabelName As Label

and then a value for this variable

    objLabelName = "lblMonth" + Trim(Str(i))
    objLabelName.Caption = strMonthLabels(i)

The answer is probably staring me in the face!

Please can you advise how to use a For-Next Loop to move from Label to Lable 
to change each caption?

Many thanks

-- 
Derek Dowle
0
Utf
11/24/2009 11:00:04 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
665 Views

Similar Articles

[PageSpeed] 7

For i = 1 To 60
UserForm1.Controls("lblMonth" & i).Caption = strMonthLabels(i)
Next i

-userfom1-(or your userform's name) may/may not be necessary depending
on where the code is placed.


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=156812

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
p45cal
11/24/2009 11:47:21 AM
Note that objLabelName is a string, so you need this:

     controls(objLabelName).caption = strMonthLabels(i)

But p45cal's code doesn't need an intermediate string.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Derek Dowle wrote:
> UserForm Query
> 
> I have a User Form which is used to collect monthly cost information for a 
> five year period for a database.   The start month and year of the five year 
> period will vary from record to record.
> 
> Intially the user enters the start month and start year into separate Text 
> Boxes.   From this information I have created an Array, strMonthLabels(60), 
> which contains each month and year for the five year period; i.e. "Jan 2009", 
> "Feb 2009", etc.
> 
> Each of the Text Boxes to capture the month cost information has an adjacent 
> Label to signify the month and year so that the user can enter the data 
> correctly.   These Labels are named lblMonth1 through to lblMonth60.
> 
> I want to be able to use a For-Next Loop to change the Captions on each of 
> the Labels.   The following indicates my intententions but does not work.
> 
>     For i = 1 To 60
>         lblMonth(i).Caption = strMonthLabels(i)
>     Next
> 
> An alternative I have tried unsuccessfully is to create a Label variable
>     
>     Dim objLabelName As Label
> 
> and then a value for this variable
> 
>     objLabelName = "lblMonth" + Trim(Str(i))
>     objLabelName.Caption = strMonthLabels(i)
> 
> The answer is probably staring me in the face!
> 
> Please can you advise how to use a For-Next Loop to move from Label to Lable 
> to change each caption?
> 
> Many thanks
> 
0
Jon
11/24/2009 4:11:40 PM
Reply:

Similar Artilces:

MS Query #6
Hi, Just for curiosity. MS Query has a status bar with seven boxes on the right. I only get 4, 5 and 6 working: [Caps], [Num] and [Scrl]. Do the other boxes have a function? And if so: what function? Thanks for a reply, Frans ...

Creating a userform?
I would like to enter data to a speadsheet with a userform. I want enter the data in a form that will automatically insert the data to the correct cells in my spreadsheet. And then allow me to enter new data on the next line below. Is this possible? I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 42 towards the top of my home page. In here I have tried to explain, as clearly as possible, what you need to do to set up and use a Form in EXCEL 2007. There is a simple worked example for you to work through if you want to. Please d...

Query Results Where Qty Shipped is not Divisable by 4
How would I construct a query to return all invoices where the qty shipped for a specific item is not divisable by 4? Really its the divisable by 4 part need assistance with. Thanks! SELECT * FROM YourTableName WHERE YourFieldName Mod 4 <> 0; -- Ken Snell http://www.accessmvp.com/KDSnell/ "meangene" <meangene@discussions.microsoft.com> wrote in message news:2684394D-463B-4804-970F-79205F41C039@microsoft.com... > How would I construct a query to return all invoices where the qty shipped > for a specific item is not divisable by 4? Rea...

Open Userforms from a Dropdown list
Hello, and thanks for the help, I have several userforms and would like to place the names of the forms into a dropdown list to select the form i wish to use is this possible. again thanks ...

'Operation must use an updateable query'
I have an issue with a query that I cannot figure out the answer to. I keep getting the error 'Operation must use an updateable query'. The query I am running is: UPDATE DGBTestVariations SET DGBTestVariations.JURISDICTION = IIF(DGBTestVariations.JURISDICTION = "NoMoneyMuni", (SELECT DGBJurisdictionCOO.JurisCodes FROM DGBJurisdictionCOO WHERE DGBJurisdictionCOO.ID=3), IIF(DGBTestVariations.JURISDICTION = "Municipalities", (SELECT DGBJurisdictionCOO.JurisCodes FROM DGBJurisdictionCOO WHERE DGBJuri...

Why is my query read-only?
Hi I have a query linking my Customers table & my Contacts table, so that all the information can be displayed together on the screen for updates and deletions. Unfortunately, the resulting data is read only. I didn't tell it to be read only. Why has this happened? Stapes Take a look at http://office.microsoft.com/en-ca/access/HP051880011033.aspx -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Stapes" <steve.staple@gmail.com> wrote in message news:1183985818.396460.60960@57g2000hsv.googlegroups.com... > Hi > > ...

Addind an external LDAP query into the Exchange 2003 Recipient Lis
Is there a way to take a query from an external LDAP server and place this list of names into a recipient list in my exchange server? I am running Server and Exchange 2003. Currently I am having my users individually setup a LDAP query address list in Outlook. This is becoming a pain as too many users would like to have access to the list now that I have it setup for a few. I have attempted to get the offline GAL from the external server administrators but they are no longer willing to distribute it and are forcing everyone to pull from the LDAP query. I know I can manually create a ...

opening a userform from a userform
Is there a way to open and use a userform from another userform? I am using userforms for data entry and would like the option to open a second form whilst entering data into a form Can anyone help? [excel 2003] Just Show it. Call is like this... UserForm2.Show or like this... UserForm2.Show vbModeless depending on whether you want UserForm2 to be modal or non-modal. Remember to change my example UserForm name from UserForm2 to whatever the name your second UserForm is. -- Rick (MVP - Excel) "Roger on Excel" <RogeronExcel@discussions.microsof...

Missing Data From Excel Query
I'm querying an Excel database into another Excel spreadsheet. I have two fields where I want users to input into the database. This input data is not coming across in my data, even though I'm including the field. Database: Account Mar Apr Variance Explain Explanation Printing 546 556 -10 Explain Printed spring catalogue The explanation field is where the input is, everything else is a formula. But even if I make the input data into a formula, it doesn't come across in the query. If I make the input data a number such as 5%, it comes across, but any text d...

check for record before update query
I need to check if a record exisists before I run the following update query. Not sure how to get there though. Table name is InspectionLog Field name is SalesOrder (text field) Here is my code which runs fine. stsoNum = InputBox("Enter Sales Order") SQLstr = "UPDATE InspectionLog SET InspectionLog.PullDate = Now(), InspectionLog.MotorPullDate = Now(),InspectionLog.MotorLoaded = True, InspectionLog.MotorPuller= [Forms]![PartsPullSwitchboard]![TechNumber], InspectionLog.PartsPullID1 = [Forms]![PartsPullSwitchboard]![TechNumber], InspectionLog.MotorLineNumber= [For...

Import query in code
I want to import data from one database to another. I have the following query that will import all the data that i need. I am not sure how to modify this query to only import data that does not already exist in the table. Any suggestions? sql = "Insert into classinfo (showid,classid,classnumber,classdescription) "SELECT ClassInfo.ShowID, ClassInfo.ClassID, ClassInfo.ClassNumber, ClassInfo.ClassDescription FROM (ActiveShow INNER JOIN ClassInfo ON ActiveShow.ShowID = ClassInfo.ShowID) where ClassInfo.SeparateScoring = True in '" & Text1.Value & "'&quo...

Query duplicates results due to many-to-many relationships
I have a query with fields from different tables. Two or three of these field may have one or more entries. When I run the query, if it displays a record where there are multiple entries for a particular field, it will list the record multiple times. An example: This DB involves officers using force - if there is more than one officer involved, each one will be listed with the applicable level of force and if the subject is arrested, there may be one or more charges enterer - assuming there are 2 officers with 3 charges, that record may be listed 5 or 6 times, but the multipl...

passwords - userforms
[Excel 2003] I have a spreadsheet which uses forms to enter and display data. The Spreadsheet uses a main page form "frmDashboard" from which other userforms are activated using command buttons. The form frmDashoboard remains open in the background as a desktop while the user activates the other forms. I am looking at various methods to password protect the opening of other forms from the main useform frmDashboard and I use the following code to do this: Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password&q...

XML query
Hi, I need to have a small, easy and portable database and i'm thinking of having a simple XML file (without using DTD, etc). Can you please let me know, as to how I can read/write into different attributes. I've given a example XML below and I need to know as to how exactly I can loop and display all the "Title" attributes seperately (i.e. description and URL). <?xml version="1.0" encoding="utf-8" ?> <Titles> <Title Description="Hotmail" URL="http://hotmail.com> <Title Description="Microsoft" U...

Query into specific columns
Hopefully, this is a simple problem: I have built a sheet with columns A-H. I query a separate Excel sheet that contains 4 columns of data that I need. I would like the data imported into columns A,B,E & G. But the query returns the data to A,B,C & D. What can I do?? Thanks ...

userform
hello everybody, is it possible to see the content of a cell (i.e. worksheet 1 cell y32) (and all it's changes) in a userform (which is also used to input data) while it is still open? Hi You will probably need some code for that, like Label1.Caption = Sheets(1).Range("Y32").Text trigged by some relevant events, like the Exit events of your input boxes. -- HTH. Best wishes Harald Followup to newsgroup only please. "Mauro" <mlauro@fastwebnet.it> wrote in message news:WlKQb.4922$VS6.4580@tornado.fastwebnet.it... > hello everybody, is it possible to see the ...

what is the query to list records with a certain field empty?
what is the query to list records with a certain field empty? Put Is Null in the criteria row of the query -- Wayne Manchester, England. "aa" wrote: > what is the query to list records with a certain field empty? > > > Thanks a lot ...

not recognising a query
the line: DoCmd.OpenQuery DELETEtbldatentry shows me an error message: DELETEtbldatentry = empty The name is correct and the query works fine when opened manually. What am I doing wrong? why post twice? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC o...

Excel VBA
Hi Excel VBA'ers, Via Google I found some great code (see below) that enables MouseDown to paste what's in clipboard. I want this code to apply to ALL of the textboxes in my Userform. Instead of repeating this code multiple times for every textbox, I was wondering how/if I can specify multiple textboxes using the one instance of the code please. (Hmm, if I'm on the right track, perhaps I have to learn about Class Modules to do this??) Here is the code that I found: Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Sing...

Web Query with Graphics
Can you import HTML data including any graphics, into a spreadsheet using Excel web query? Or is there a way to import data from a web page into Excel and keep the formatting in tact. Especially any graphics such as bullets? ...

Basic Update Query
I have the following two tables Invoice-InvoiceProduct and there's a many to one relation between them, I use the following query to work out the total base on all the InvoiceProducts that share the same InvoiceNumber but it returns empty anythoughts? UPDATE Invoice SET Invoice.Total = Sum(InvoiceProduct.SubTotal) WHERE (((Invoice.InvoiceNumber)>0)); InvoiceProduct ========= InvoiceNumber ItmeID Quantity Price SubTotal 10 1 2 $100 $200 <- this update query works fine 11 2 5 $8 ...

Query too Complex...
words I have seen way too much of today! And if it is not "too compex" then I get a message saying "insufficient resources". I have spent the day trying to break up boards into smaller sections and trying to find other ways to get the same answer. Eventually I have to bring the seperated boards back together again, so the end result is still the same: Query is too complex Does anyone have a suggestion that I could try to get around these frustrating comments? Should I be using subqueries? - would that help simplify things in the FROM clause? Tara Without seeing ...

Need Query Help
Hi All, I am new to Access. I built a query which gives me purchase orders and payments on those purchase orders. However, the query dpes not give me purchase orders with no payments. I need both: (1) purchase orders with payments and (2) and purchase orders without payments. How do I go about adjusting my query to include both? -- Shirley In your query, select the line that joins your two tables and right-click. That should bring up a context menu with a choice for Join Properties. Select that, then select the option that will include ALL records from the purchase order table ...

Blank Userform
Hi all, I have a slight problem, I have some code which adds rows with formfields in to a table in a protected form. These formfields have text in which is dependant upon users ticking checkboxes. Before adding the rows my code checks to see if particular rows have already been added to the table so that these rows are not added again. The whole macro takes about 30 seconds to run and to save users seeing Word 'dance' and the screen flash, I have tried to utilise a userform to appear for the duration of the code. Unfortunately the body of the userform turns bla...

Vlookup in a userform
Hi, If I enter a number into a userform in a box called 'reference' can I then use a vlookup to fill in the next box 'name' from an external file? I can use the vlookup on a sheet but am not sure of the syntax? within VBa. TIA, Rick With such a generic question a specific answer isn't possible. But in general if you can write a formula in a cell that returns the result you want, yes, you can do it from VB. -- Jim Rech Excel MVP "R D S" <RDS@members.v21.co.uk> wrote in message news:35nl2qF4pjv34U1@individual.net... | Hi, | If I enter a number into a...