Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    I'm a newby in learning mode. My questions ought be a no-brainer to many of you, but I am just not getting this.

    Question 1:
    I need to create sbSpinButton1 whose value is an integer from 1 to 99. This value needs to be reflected in tbTextBox1.

    Question 2:
    I need to create sbSpinButton2 whose value is a capital letter from A-Z and then AA to ZZ (after Z comes AA). This value needs to be reflected in tbTextBox2.

    My attempts have resulted in skipping a letter (i.e. A, C, E, G, etc.).

    Since I feel like I'm poking around in the dark, I would really appreciate it if someone would shed some light on this spinbutton thing for me.

    Thanks!!
    Troy <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    I hope I understand it correctly. I created a UserForm, called UserForm1. I added two textboxes: textbox1 and textbox2. Next to each textbox, add a spinbutton: SpinButton1 and SpinButton2. To prevent users to enter something in the textboxes, I use images to put on top of these textboxes and make them transparent (this can be done using the image properties). I do this because I only want the textbox values to be changed by clicking the spinbuttons.
    The first textbox (TextBox1) and spinbutton (SpinButton1) is used to change the number from 1 to 99. The second textbox (TextBox2) and spinbutton (SpinButton2) is used to change the value from A to ZZ.
    Here is the code attached to the Userform:

    <pre>Option Explicit
    Dim Number As Integer
    Dim AlphaNumber As Integer
    Dim q As Integer
    Private Sub SpinButton1_SpinDown()
    If Number > 1 Then
    Number = Number - 1
    TextBox1.Value = Number
    End If
    End Sub
    </pre>


    <pre>Private Sub SpinButton1_SpinUp()
    If Number < 99 Then
    Number = Number + 1
    TextBox1.Value = Number
    End If
    End Sub
    </pre>


    <pre>Private Sub SpinButton2_SpinDown()
    If AlphaNumber > 0 Then
    AlphaNumber = AlphaNumber - 1
    q = Int(AlphaNumber / 26)
    If AlphaNumber < 26 Then
    TextBox2.Value = Chr(65 + AlphaNumber)
    Else
    TextBox2.Value = Chr(64 + q) & Chr(65 + AlphaNumber - q * 26)
    End If
    End If
    End Sub
    </pre>


    <pre>Private Sub SpinButton2_SpinUp()
    If AlphaNumber < 27 * 26 Then
    AlphaNumber = AlphaNumber + 1
    q = Int(AlphaNumber / 26)
    If AlphaNumber < 26 Then
    TextBox2.Value = Chr(65 + AlphaNumber)
    Else
    TextBox2.Value = Chr(64 + q) & Chr(65 + AlphaNumber - q * 26)
    End If
    End If
    End Sub
    </pre>


    <pre>Private Sub UserForm_Initialize()
    Number = 1
    TextBox1.Value = Number
    AlphaNumber = 0
    TextBox2.Value = Chr(65 + AlphaNumber)
    End Sub
    </pre>


    Finally you have to add a general module to add the subroutine from which you fire the Userform, using UserForm1.Show.
    I have done this in Excel, but it should be equivalent in Word.

  4. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    Hans,

    If the Enabled property of the Textbox is set to false, then the user cannot interact with it, but the spinbutton events (or any other code) could assign a value. This should dispense with the need to use pictures. You could set the property at design time or use :<pre>TextBox1.Enabled = False</pre>

    in the UserForm_Initialize() event.

    Andrew C

  5. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    Actually, Andrew, I used this 'disabling' in the past and had completely forgotten about it. I learned the trick with the image overlay in my Visual FoxPro period to keep control over the colors. I thought that when disabling the textbox, the font color turns into grey. But you are right, if you don't mind about this, you can just disable the textboxes to prevent entering data directly into the textbox.
    Thanks, as you see, never too old to learn.

  6. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    Thanks so much for your response. This did work for me. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    However, as I am in learning mode, I did have a couple of questions and one additional request <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>:

    Question 1: What is the purpose or function of the following line in the code you gave me:

    q = Int(AlphaNumber / 26)

    Question 2: What is the purpose or function of the following line in the code you gave me:

    TextBox2.Value = Chr(64 + q) & Chr(65 + AlphaNumber - q * 26)

    Request 1: As much as I appreciated your suggestions for disabling the text box for user input, I actually want users to be able to manually input, as well as use spinbuttons. However, when I tried to do this using the code you gave below (I only changed the variable names to protect the somewhat innocent) and then used a spin button, it only accepted the value of the spinbutton. I tried putting the following code in the change event for the text box:

    SpinButton1.Value=TextBox1.Value

    but that didn't seem to do anything. If you could provide this extra bit of information, I'd sure appreciate it.

    Thanks!!
    Troy

  7. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    Each character has an alphanumeric code: A corresponds to 65, B to 66 etc. Other characters have other alphanumeric codes, ranging from 1 to 255. The last letter in the alphabet, capital Z has code 90. To convert the alphanumeric code to a character, you can use the command chr(alphanumeri code). So, A = chr(65). Now, you asked for a spinbutton allowing you to go from A to ZZ. As there are 26 characters in the alphabet, you start from code 66 (= A) up to 90 (=Z). But 91 does not correspond to AA. You have to consider the second character as completely independent. Therefore, as the cycle has a length of 26 (the number of characters in the alphabet) you have to define an extra variable q that counts for this. The spinup event of the spinbutton object actually increases the variable alphanumber with 1. When this value becomes 91, you want "AA" to be displayed. To do this you need two characters, the first one defined by q, the second one defined by what is left over. Compare it with hours and minutes, or with the decimal system; it is analogous. Textbox2.value contains the first character chr(64+q) and the second character chr(65+alphanumber-q*26).
    If you want to let the user enter values in the textboxes and continue using the spinbuttons you need to build in a few controls for what the user actually enters. You also need to update the values for the spinbuttons: that is, the variable AlphaNumber for textbox2 / spinbutton2 and the variable Number for textbox1 / spinbutton1. Letting the user doing the input is not a good idea, because you will need to check if he/she enters a correct data type (e.g. in case of textbox1, you need to check if he/she enters an integer between 1 and 99; in case of textbox2 it will be a little bit more difficult).

  8. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    Troy,

    I have to admit that I could have made it less complicated if I had used the spinbutton properties Min, Max and Value. I changed my code here and there and in the meantime I tried to link the Spinbutton to the Textbox, allowing manual input from your users. When the spinbutton's change event is triggered, the textbox is updated. Vice versa, if the user enters a value directly into the textbox, its change event is triggered and then the spinbutton value should be updated. In case of textbox2, I used the exit event, allowing to wait for evaluating the contents of the textbox on exiting. Some additional checks on entries might be necessary. Here it is:

    <pre>Option Explicit
    Dim q As Integer
    Dim AlphaNumber As Integer
    </pre>


    <pre>Private Sub SpinButton1_Change()
    TextBox1.Text = SpinButton1.Value
    End Sub
    </pre>


    <pre>Private Sub SpinButton2_Change()
    AlphaNumber = SpinButton2.Value
    q = Int(AlphaNumber / 26)
    If AlphaNumber < 26 Then
    TextBox2.Value = Chr(65 + AlphaNumber)
    Else
    TextBox2.Value = Chr(64 + q) & Chr(65 + AlphaNumber - q * 26)
    End If
    End Sub
    </pre>


    <pre>Private Sub TextBox1_Change()
    Dim NewEntry
    NewEntry = Val(TextBox1.Text)
    If NewEntry >= SpinButton1.Min And NewEntry <= SpinButton1.Max Then
    SpinButton1.Value = NewEntry
    End If
    End Sub
    </pre>


    <pre>Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim NewEntry As String
    NewEntry = TextBox2.Text
    If Len(NewEntry) = 0 Or Len(NewEntry) > 2 Then
    MsgBox "This entry does not match"
    Cancel = True
    Exit Sub
    ElseIf Asc(Left(NewEntry, 1)) < 65 Or Asc(Left(NewEntry, 1)) > 90 Or _
    Asc(Right(NewEntry, 1)) < 65 Or Asc(Right(NewEntry, 1)) > 90 Then
    MsgBox "This entry does not match"
    Cancel = True
    Exit Sub
    End If
    If Len(NewEntry) = 1 Then
    AlphaNumber = Asc(NewEntry) - 65
    Else
    AlphaNumber = (Asc(Left(NewEntry, 1)) - 64) * 26 + Asc(Right(NewEntry, 1)) - 65
    End If
    If AlphaNumber >= SpinButton2.Min And AlphaNumber <= SpinButton2.Max Then
    SpinButton2.Value = AlphaNumber
    End If
    End Sub
    </pre>


    <pre>Private Sub UserForm_Initialize()
    SpinButton1.Value = 1
    SpinButton1.Min = 1
    SpinButton1.Max = 99
    SpinButton2.Value = 0
    SpinButton2.Min = 0
    SpinButton2.Max = 27 * 26 - 1
    TextBox2.Value = Chr(65)
    End Sub
    </pre>


  9. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating textbox/spinbuttons for characters/digits (VBA for Word 2000)

    This did the trick. I learned a lot through studying your code and making a few modifications here and there. Thanks for taking the time!!

    Troy

Posting Permissions

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