• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract single letter from string on Combo Box Change Event

M

Maneesh Massey

Member
Hi,
I have a Text box and Combo box on a UserForm in Excel 2013. The Combo box has some numbers from 1 to 10. I would like to have the Text box update the selections made from the Combo box like so:

If 1 or 2 or 3 is selected from the Combo box, then Text box should display only the letter W (in uppercase for Western). If 4 or 5 or 6 or 7 is selected, Text box should display only the letter C (in uppercase for Central) and if 8 or 9 or 10 is selected, then Text box should display the letter E (in uppercase for Eastern).

All help is greatly appreciated.
Thanks.
Regards,
Maneesh
 
Hi Maneesh ,

Try this :

Code: 
Private Sub ComboBox1_Change()
            Dim outchar As String
            Select Case ComboBox1.Value
                  Case 1, 2, 3
                        outchar = "W"
                  Case 4, 5, 6, 7
                        outchar = "C"
                  Case Else
                        outchar = "E"
            End Select
            TextBox1.Value = outchar
End Sub
Narayan
 
Hi NARAYANK991,
Thanks for reply to the post. Your method worked very well.

Actually, I have other textboxes on the form which make up an ID number. I am running this code to update the Textbox which contains the ID.

Code: 
Private Sub UpdatePID()
Me.Arec6 = Me.Arec2 & Left(Me.Arec7.Value, 1) & Mid$(Me.Arec7.Value, InStr(Me.Arec7.Value, " ") + 1, 1) & _UCase(Replace(Mid(Me.Arec4, 1, 5), " ", "")) & Format(Me.Arec5, "000000")
End Sub

There is 2 Combo boxes and 1 text box on the form that make up the ID. The first Combo box is called Arec2 and second is called Arec3. The text box is called Arec4. The two Combo boxes are cascading and dependent. If the user chooses a number (1 to 10) from the first Combo box, the second updates with a district name corresponding to the selection made in the first. The text box is where the user enters a number. All of this gets updated to a third text box on the form called, Arec6.

The entry from the first Combo box should be the first to be displayed in Arec6 text box.
The code I'm using is below:
Code: 
Private Sub Arec2_Change()
    If Me.Arec2.Value <= 2 Then
                        Me.Arec6.text = "W"
                ElseIf Me.Arec2.Value <= 7 Then
                        Me.Arec6.text = "C"
                        
                Else
                        Me.Arec6.text = "E"
                       
                End If
   End If

Private Sub UpdatePID()

Me.Arec6 = Me.Arec2 & Left(Me.Arec7.Value, 1) & Mid$(Me.Arec7.Value, InStr(Me.Arec7.Value, " ") + 1, 1) & _ UCase(Replace(Mid(Me.Arec4, 1, 5), " ", "")) & Format(Me.Arec5, "000000")

End Sub

Private Sub Arec4_Change()
  UpdatePID
 End Sub

Private Sub Arec5_Change()
 UpdatePID
End Sub

Example Output: WKANP000007 (where W is for Western, KANP is for Kanpur Nagar and 000007 is the ID number the user has to enter into Arec5 text box.

Any ideas on how this could be done ? The code I gave above is not working.

Thanks for your time.
~Regards,
Maneesh






NARAYANK991 said:
Hi Maneesh ,

Try this :

Code: 
Private Sub ComboBox1_Change()
            Dim outchar As String
            Select Case ComboBox1.Value
                  Case 1, 2, 3
                        outchar = "W"
                  Case 4, 5, 6, 7
                        outchar = "C"
                  Case Else
                        outchar = "E"
            End Select
            
TextBox1.Value = outchar
End Sub
Narayan
Click to expand...
 
Hi Maneesh ,

Clarity of communication is a sine qua non for problem resolution ; first there was a watered down description of the problem , which had no relation to the real-life situation ; now there is an excess of description which only increases the confusion.

What are the objects we are concerned with ?

1. Combobox 1 : named Arec2
2. Combobox 2 : named Arec3
3. Textbox 1 : named Arec4
4. Textbox 3 : named Arec6

What is the user interaction ?

1. Choose a number between 1 and 10 from the first combobox , Combobox 1
2. Enter a number into the textbox Arec5

What happens within the code ?

1. Combobox 2 updates with a district name , based on the selection made in Combobox 1.

You also mention Arec7 in the code.

Going through the code , I can see that when Arec2 changes , it updates Arec6 to "C" , "W" or "E".

I assume that Arec4 is the textbox where the user enters 000007 , though you say it is Arec5.

I assume that Arec3 is updated to contain KANP.

It is all totally confusing. If you can upload your workbook , it would make things easier.

Narayan
 
Hi,
I think I should upload you the application I'm working with.
I've uploaded the app on SkyDrive. Here's the link:
https://skydrive.live.com/redir?resid=FD076044CD1CB10A!134&authkey=!AFS2aalP_agkaRA&ithint=file%2c.xlsm
Please let me know if its working.

The form I'm working on is called the frmAdd. It has other text boxes which I did not mention for the reason that I thought it would just a simple problem, I do not have to mention the whole application. But as requested by you, I've uploaded it on SkyDrive.

frmAdd has Combo boxes and Text boxes. This is just a simple form which enters an Agent's personal details and work details. The user has to choose a Zone from Arec2 Combo Box, choose a district from the Arec4 Combo Box, and write down an ID number in Arec5 Text Box.
Then all of these 3 pieces of information go into the PID Text Box (Arec6). While the rest of the information deals with Agent's personal information.

All I want is that when the user chooses a Zone from Arec2 Combo Box, depending on the Zone number, Arec6 Text Box should get updated and display either "W" or "C" or "E" BEFORE the district name and ID number. So for example, an entry would look like: WKANP000077 W for West; KANP for Kanpur Nagar; and 000077 is the ID number. This is all I need done.

Thanks once again for the help rendered.
Regards,
Maneesh
 
NARAYANK991 said:
Hi Maneesh ,

Clarity of communication is a sine qua non for problem resolution ; first there was a watered down description of the problem , which had no relation to the real-life situation ; now there is an excess of description which only increases the confusion.

What are the objects we are concerned with ?

1. Combobox 1 : named Arec2
2. Combobox 2 : named Arec3
3. Textbox 1 : named Arec4
4. Textbox 3 : named Arec6

What is the user interaction ?

1. Choose a number between 1 and 10 from the first combobox , Combobox 1
2. Enter a number into the textbox Arec5

What happens within the code ?

1. Combobox 2 updates with a district name , based on the selection made in Combobox 1.

You also mention Arec7 in the code.

Going through the code , I can see that when Arec2 changes , it updates Arec6 to "C" , "W" or "E".

I assume that Arec4 is the textbox where the user enters 000007 , though you say it is Arec5.

I assume that Arec3 is updated to contain KANP.

It is all totally confusing. If you can upload your workbook , it would make things easier.

Narayan
Click to expand...
Hi Narayan,
Still waiting for your response. Please help. Hope you could open the file uploaded on SkyDrive.
Regards,
Maneesh
 
Hi Maneesh ,

When I try to access the file , I get the following message :

This item might not exist or is no longer available

This item might have been deleted, expired, or you might not have permission to view it. Contact the owner of this item for more information.

Please see what is to be done.

Please note that this forum has a facility to upload files less than 1 MB in size ; if your file is within this limit , you can upload it ; if not , you can use external file-sharing websites.

Narayan
 
Hi Maneesh ,

I could open the file UserFormTESTSKYDRIVE.xlsm ; everything seems to work ; can you indicate what exactly is the problem ?

Narayan
 
Hi,
Okay, good everything is working. On the frmAdd you will see 2 Combo Boxes and a lot of Text boxes. Particularly I'm concerned with the PID frame. There you will find the Zone and District Combo Boxes and a 6-digit ID Text box. Down below in the other frame, you will find the PID Text box. The Zone Combo Box has some numbers (1 to 10) Zones (1 to 3 for Western) Zones (4 to 7 for Central) and Zones (8 to 10 for Eastern) on which the District Combo Box is dependent. The user selects a zone from the Zone Combo Box, a district from the District Combo Box and then enters a 6-digit ID number and a name in the Name Text Box. All these pieces of information go into the PID Text Box which comprises an ID number. I want when the user selects a Zone from the Zone Combo Box, depending on the selection of the zone number, the PID Text box should be displayed as:W or C or E before the Agent name in the Text box. For example, WMMKANP000007 would be the ID number, 'W' for Western (zone); MM for Maneesh Massey (name) KANP for Kanpur Nagar (district) and 000007 is the 6-digit ID number that the user has to enter.

I hope I've clearly explained everything. Please, let me know if more information is needed.
Thanks and Regards,
Maneesh
 
Hi Maneesh ,

The objects we are concerned with are :

1. The Combobox Arec2 , whose label is Zone
2. The Combobox Arec4 , whose label is District
3. The Textbox Arec5 , whose label is 6-Digit ID
4. The Textbox Arec6 , whose label is PID

When I select 2 in the Zone combobox ( Arec2 ) , it is immediately reflected in the PID textbox ( Arec6 ) as 2 ; should this become "W" at this stage itself ?

Narayan
 
Hi Maneesh ,

Can you try with this code ?

Code: 
Private Sub UpdatePID()
            On Error Resume Next
            Dim outchar As String
            Dim j As Integer
            For j = 6 To 10
                If Trim(Me("Arec" & j).text) = "" Then Exit For
            Next
               
            Me("cmdnext").Visible = j = 12
           
            Select Case Me.Arec2.Value
                  Case 1, 2, 3
                        outchar = "W"
                  Case 4, 5, 6, 7
                        outchar = "C"
                  Case Else
                        outchar = "E"
            End Select
           
            Me.Arec6 = outchar & Left(Me.Arec7.Value, 1) & Mid$(Me.Arec7.Value, InStr(Me.Arec7.Value, " ") + 1, 1) & UCase(Replace(Mid(Me.Arec4, 1, 5), " ", "")) & Format(Me.Arec5, "000000")
            On Error GoTo 0
End Sub
Narayan
 
NARAYANK991 said:
Hi Maneesh ,

Can you try with this code ?

Code: 
Private Sub UpdatePID()
            On Error Resume Next
            Dim outchar As String
            Dim j As Integer
            For j = 6 To 10
                If Trim(Me("Arec" & j).text) = "" Then Exit For
            Next
              
            Me("cmdnext").Visible = j = 12
          
            Select Case Me.Arec2.Value
                  Case 1, 2, 3
                        outchar = "W"
                  Case 4, 5, 6, 7
                        outchar = "C"
                  Case Else
                        outchar = "E"
            End Select
          
            Me.Arec6 = outchar & Left(Me.Arec7.Value, 1) & Mid$(Me.Arec7.Value, InStr(Me.Arec7.Value, " ") + 1, 1) & UCase(Replace(Mid(Me.Arec4, 1, 5), " ", "")) & Format(Me.Arec5, "000000")
            On Error GoTo 0
End Sub
Narayan
Click to expand...

Hi Narayan,
Thanks a lot for helping me. At last the code is working !!! Appreciate all the help !
Regards,
Maneesh
 
You must log in or register to reply here.
Back
Top