Caption Multiple Labels Based On Cells

    I am displaying cells in textboxes depending on the results of a listbox selection. I was wondering if there is code for making the textbox names variable (so I could just loop through the offsets and textbox names)


    the code below works but I would like to make it more versatile and shorten it.
    There are other textboxes on the page.



    thankyou for any assistance


    Justin

    Re: Loop Fill Multiple Sequential Textboxes


    Thankyou for the reply,


    It looks a little complicated still but I am tired (3am), so I will have another look in the morning and hope it makes some sense.


    From a quick glance though it looks like all objects/shapes will be looped which might be a problem.


    Thanks again for the pointer.


    Justin

    Re: Loop Fill Multiple Sequential Textboxes


    How about something like this:

    Code
    Dim i As Long
    
    With Sheet2.Range("A1")
        For i = 2 To 15
            Sheet3.OLEObjects("Label" & CStr(i)).Object.Caption = .Offset(, i - 2).Value
        Next i
    End With

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Loop Fill Multiple Sequential Textboxes


    Thankyou shg, I have tried the code and cant get it to work from a module but I will keep trying as it appears to be exactly what I am after.


    Thanks for the querie norie, the question seems a little difficult to answer without knowing why you ask it... are you suggesting a more efficient way of achieving the same result? If so I am happy for any pointers.


    Cheers


    Justin

    Re: Loop Fill Multiple Sequential Textboxes


    Hmmm -- There's not much to it, and I did test it.


    Want to post a small piece of a workbook that illustrates the problem?

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    If you wish to continue using this free service.


    Please be considerate of others who use the forum for searching. Your current Thread Title (which I will change) is non-reflective of your immediate problem.


    In future, please take 1 minute of your time to read the text on the New Thread page.


    [fa]*[/fa]


    REMEMBER: Your thread title should NEVER be what you THINK is your answer. 9 times out 10 it will wrong and prevent someone from finding a solution to a simliar issue.

    Re: Caption Multiple Labels Based On Cells


    Hi shg,


    thankyou for the offer, I have managed to cut the file to fit in a zip less than 45kb (which took some working out, it was odd the way the file would get larger as I deleted things)


    the relevent code is in module1 and is called "sub detailfill()", your code is below.


    As mentioned by Dave I was after the solution for textboxes since the labels will remain constant. I assume you just change your code to reflect this, however when I tried it as given with labels that were beside each of the text boxes it did not work.



    Thankyou for the help


    Justin

    Re: Caption Multiple Labels Based On Cells


    Justin


    Sorry if my question was unclear but it's also unclear what you are trying to do.


    And it makes it even more difficult to understand when the workbook you attached constantly errors out.:)

    Boo!:yikes:

    Re: Caption Multiple Labels Based On Cells


    Hi Norrie,


    Sorry for the errors, it was working when I posted it but I didnt try anything too fancy (since I deleted most of the objects to get the size down it was a bit difficult to ensure all code was up to speed).


    I will give the question another shot:


    What I am trying to do is have the textboxes on the page called "detail" refresh with contents from a cell depending on a listbox _change event (selection). The code that is in the workbook now works fine (when all the objects are available) but I was hoping to loop through the textboxes to fill them rather than have them each take up a line of code.


    the code that shg provided looks like what I am after,


    Code
    Dim i As Long 
 
With Sheet2.Range("A1") 
    For i = 2 To 15 
        Sheet3.OLEObjects("Label" & CStr(i)).Object.Caption = .Offset(, i - 2).Value 
    Next i 
End With


    except that it uses label instead of textbox, I was hoping I could just change "Label" to "Textbox" and the .Caption to .Text it would work. I have not spent hours trying to get this to work but I will look at it some more.


    As I said it looks right, I just havent got the syntax yet, either for the textboxes or the labels. Is there any reason it might work on a form only?


    Thankyou for any advice.


    Justin


    P.S. The following is irrelevant information included incase you are looking at the spreadsheet I attached.


    I have a page that I am using as a form, called details in the .xls I attached previously.


    On the intro page (called front) I have a drop down list from which you select categories, the category selected determines the dropdown list for the next combobox.


    below the second combo box is a text box in which you can bypass the first two combobox and text is searched as you type it (searches all cells in the used range of the "source" sheet).
    The list box below these fills with matches to the searches above (from the sheet called source)


    when you click on the listbox it takes you to the details page and updates the textboxes there with the selected item (the original sheet included labels which held the header cell information and textboxes which are still there hold the cell pertinant to the selected item (row))


    I was going to make them both fill on each refresh (selection) so that the database could be changed (headers and all) at any time without having to change code but I gave up on that idea.

    Re: Caption Multiple Labels Based On Cells


    Justin


    I really can't see what you are trying to do.


    When I try and find textboxes on your worksheet Excel tells me there are no objects.


    When I unhide the details sheet and play about a bit with it Excel fatally crashes.:huh:


    Perhaps you could explain what the overall purpose is?


    PS Have you tried using a userform? And instead of multiple textboxes displaying the results in another control, eg a listbox?

    Boo!:yikes:

    Re: Caption Multiple Labels Based On Cells


    HI norie,


    In my spreadsheet/database called source there will be a number of chemicals (with the chemical name being unique identifier in column A). In column B, C etc... are the CAS number (an identifying number), the scientific name and other such information.


    The "front" sheet is a search page.


    the "details" sheet is an output page.


    included in the details (output) page is a listbox with the results of a search (multiple or single results depending on what the search returns). When you make a selection in the listbox the results for that selection are displayed in the textboxes.


    At the moment each text box is filled line by line eg.


    Code
    sheet3.textbox1.text = sheet2.range("$A$1").offset(y, x).value


    this works fine, I was just wondering... since the textbox name are sequential (TB1, TB2 etc..) could you use code to loop through them one at time and fill them rather than having to use a line of code for each textbox as I currently am.


    I think the code provided by shg will do this once I have the syntax right.


    Thanks again for the time.


    Justin

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login