+ Reply to Thread
Results 1 to 5 of 5

UserForm Labels Drawn from Cell in Sheet

Hybrid View

  1. #1
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: UserForm Labels Drawn from Cell in Sheet

    Have you stepped through the code with F8 to see what's actually happening?

    By the way, couldn't you also display the # of bonds and Issuer on the first userform as well as in cells?

    Then when you show the second userform you can populate the labels on it from the first userform.

    PS Instead of calling the Initialize event you should just use this to show the second userform.
    BondAddUserForm.Show
    PPS Can you add code tags when posting code?
    If posting code please use code tags, see here.

  2. #2
    Registered User
    Join Date
    12-28-2015
    Location
    Portland, OR, USA
    MS-Off Ver
    365
    Posts
    3

    Re: UserForm Labels Drawn from Cell in Sheet

    Thanks for the input - I used F8 (unaware of that function, super-useful) and cleaned up a bunch of the code on the first userform as a result. I made all the variables I was storing in cells on the activesheet public and still set them via the first userform.

    The reason I was using the second userform is twofold: First, the data on the first userform needs to be run through some calculations to recommend a number of bonds to buy (this may be more or less than the target value established earlier in the process depending on the bond price). Second, if the user is targeting, say, $10K in bonds for 2022 maturity, but there are only $5K available to purchase, I need to be able to get that input from the user and adjust the spreadsheet accordingly.

    The first userform code ends like this (using .show as you suggest):

    If Taxable.Value = "True" Then
        
        TaxIssue = "Taxable"
        MaxNum = Application.WorksheetFunction.RoundDown(TargetValue / BondPriceValue, 0)
    
    Else
        
        TaxIssue = "Muni"
        MaxNum = Application.WorksheetFunction.Floor(TargetValue / BondPriceValue, 5)
    
    End If
    
    Investable = BondPriceValue * MaxNum
    
    'Provide suggested number of bonds to add to ladder. Ask user if bonds to be added, and if answer is Yes, provide userform for finalizing entry into bond ladder.
    
    BondAddUserForm.Show
       
    End Sub
    And then this is the code for the second userform:

    Private Sub BondAddUserForm_Initialize(IssuerTxt, TaxIssue, Coupon, BondPriceValue, MaxNum, Investable, TargetValue, MaturityYear)
    
    '*********************************Open NewBondUserForm to Gather New Data for Bond Purchase**************************************************************
    'Remind user of issuer and suggested purchase number
    
    Label1.Caption = "You are adding a " & IssuerTxt & " bond to the ladder"
    Label2.Caption = "You can add up to " & MaxNum & " bonds to the ladder"
    
    BondAddUserForm.Show
    
    End Sub
    Still not getting the captions to pop up. All the variables then push through correctly to the calculations that are made when clicking "OK" on the BondAddUserForm. The only issue is the lack of captions showing up on the BondAddUserForm...

    Many thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Userform labels to repeat/autofill exactly into other labels on the same userform
    By indub in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-21-2015, 02:18 PM
  2. Excel Auto Due Drawn Sheet Problem
    By AVG123 in forum Excel General
    Replies: 1
    Last Post: 09-03-2015, 01:47 AM
  3. Excel Due Drawn Sheet Problem
    By AVG123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-27-2015, 03:08 AM
  4. Excel Auto Due Drawn Sheet Problem
    By AVG123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-27-2015, 02:45 AM
  5. [SOLVED] VBA Userform: Inserting cell data into useform labels
    By B Eccles-Williams in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-21-2014, 10:30 AM
  6. [SOLVED] Add Sheet Names to Userform Labels
    By zimboo in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-21-2013, 03:26 PM
  7. [SOLVED] Change values of labels in a userform through another userform
    By Zhennie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2009, 05:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1