+ Reply to Thread
Results 1 to 10 of 10

Code to enter caption text for labelControl or code to activate textboxControl text

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Code to enter caption text for labelControl or code to activate textboxControl text

    Hi everyone,
    It will be obvious I am a nooby in this great group of thinkers, but here goes anyway;

    Really there are two questions that each answer could be the solution to the problem; I have a userform similar to a message box that I am trying to add a label that will have a changing caption based upon a changing value in a particular cell on the active worksheet. I am pretty sure I can figure it out if I can just get the caption to show up on the userform based upon the code rather than the properties caption method. So that's question 1 - how to code the label to make the caption appear on the userform.

    Question 2 - ha ha. I laugh because I have been working on this for so long and it seems that the answer SHOULD be so darn simple that my question was going to be very strongly worded indeed. (I strongly word the question to myself all the time!!) So, gentle now - I can use a textbox to do the same thing as question # 1 , except I cannot figure out how to get the text to show up without the user beginning to type into the textbox. So..........How the heck, (easy now), do I activate a textbox without user intervention? In this fashion I can use a textbox as a label if I need to. I can use this answer in so many places!

    My remaining two hairs will remain if only someone can please just answer the two questions above. I will be forever grateful!

    In your debt,
    JasonRay

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,132

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    Ok

    At the top of your userform code dim a variable.

    Please Login or Register  to view this content.
    or String as appropriate
    in the userform edit page click on your text box and it should open vb and show something like:

    Please Login or Register  to view this content.
    change that to read

    Please Login or Register  to view this content.
    create a separate bit of code by selecting the text box name on the left tab and enter on the right tab.

    This will open up something like

    Please Login or Register  to view this content.
    change that to read:


    Please Login or Register  to view this content.
    That's all you need to do.
    Now whenever you click on textbox1 its value is saved.
    if you change that value, it automatically reverts to the saved value, instantaneously.

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,132

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    With regards to your caption.

    anywhere in your userform code:

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    Mehmetcik,
    Thank you so much.
    I will try it out tomorrow. Been looking at this stuff all day and am exhausted.
    Will let you know how it works out.

    Have a nice day.
    JasonRay

  5. #5
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    Mehmetcik,

    Neither worked, I am sure because I have failed to understand something.
    The following code is how I did the label captioning.

    Private Sub UserForm_Click()
    Dim LastNumber As String

    End Sub
    Private Sub Label1_Click()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    'the text in worksheet 1 cell A1 that should appear when the userform shows is "This is a test of captioning a label via vba"
    Label1.Caption = ws.Range("A1").Value

    End Sub

    Not sure why, but it comes up empty. Please note I have made sure the caption line in the properties for label1 is empty so that the caption can be shown from the code. (seems logical anyway)
    Also - as an aside, in your reply, can you please let me know how to post the code in the boxes like you did? It seems much more easily legible to separate the code that way. Thanks.
    Regarding the textbox code, I will respond separately next.

    JasonRay

  6. #6
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    Mehmetcik,
    The advice you proffered for making a textbox show updated text without user intervention didn't make sense to me on many levels.

    It seems as if you are trying to get the textbox to show the last value entered, when I am trying to get the textbox to show a value from a cell without user intervention. In other words - I am attempting to use the textbox as a label wherein the user will never interact with it, but instead, when the userform runs it will reveal a value that may or may not have changed in cell a1, worksheet 1.


    I can get the textbox to show the value as long as the user "activates" the textbox by attempting to type into it, but what I really need the textbox to do is show the text from a1 with no user intervention.
    The question boils down to, I guess, how do I get the textbox to "activate" as soon as the userform is called by the user without the user doing anything with the textbox?

    It occurs to me that your answer may indeed do exactly what I have asked above, but I think not as your last statement states ............"Now whenever you click on textbox1 its value is saved"........

    If I am all wrong, I appreciate your patience and would be grateful for clarification.

    JasonRay

  7. #7
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    Mehmetcik,
    Regarding the textbox I have tried the following two codes with no success:

    Private Sub TextBox1_Change()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    With UserForm1.Show

    TextBox1.Value = ws.Range("A1").Value

    End With

    End Sub


    and....


    Private Sub TextBox1_Change()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")

    If UserForm.Show Then

    TextBox1.Value = ws.Range("A1").Value
    Else
    TextBox1.Value = ""

    End If

    End Sub

    Being so new with this I am sure it is an elementary fundamental misunderstanding based upon my ignorance. (I have a couple of books I am reading to teach myself, but I keep practicing then run into an issue such as this. Thanks for your help)

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  9. #9
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Thumbs up Re: Code to enter caption text for labelControl or code to activate textboxControl text

    JosephP,
    You are the man!! The key I was looking for was "initialize". (I did say it would be an elementary issue). I was able to get it to work on both textbox and label.
    Such a simple thing to bring so much joy.

    Thank you so much.
    One day I shall pay it forward.

    Have a nice day, week, month, year and life.
    JasonRay

  10. #10
    Registered User
    Join Date
    03-01-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Code to enter caption text for labelControl or code to activate textboxControl text

    Mehmetcik,
    Thank you for your assistance.
    No need to continue unless you think you have something that would help me even more.
    JosephP did a good job of assisting me up to completion. I am sure you know what he advised me, but if not check out his answer.

    In any event.
    Thank you.
    Cheers,
    JasonRay

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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