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

Topic

New Reply

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

Reply | Quote

Replies

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:

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

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

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

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

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

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.

Reply | Quote

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 :

TextBox1.Enabled = False

in the UserForm_Initialize() event.

Andrew C

Reply | Quote

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.

Reply | Quote

Thanks so much for your response. This did work for me.

However, as I am in learning mode, I did have a couple of questions and one additional request :

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

Reply | Quote

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).

Reply | Quote

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:

Option Explicit
Dim q As Integer
Dim AlphaNumber As Integer

Private Sub SpinButton1_Change()
   TextBox1.Text = SpinButton1.Value
End Sub

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

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

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))  90 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

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

Reply | Quote

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

Reply | Quote