Text Box Updating

I am having trouble getting a text box to auto fill from a choice made from a 
combo box.

Combo Box Name is Address_21
Text Box Name is City_1

My code is as follows:

'Auto Fill CES Spec Written Office

Private Sub Address_21_Change()

  Select Case Address_21
     Case ""2306 Long Green Court" 
         City_1 = "Valrico"
     Case ""3440 Oakcliff Road" 
         City_1 = "Atlanta"
     Case "7015 East 14th Aveneue" 
         City_1 = "Tampa"
     Case "1800 Taylor Drive" 
         City_1 = "Gastonia"
  End Select

End Sub

Can someone help me figure out what I did incorrectly?


0
Utf
12/17/2009 2:47:04 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
525 Views

Similar Articles

[PageSpeed] 49

Try this
Private Sub Address_21_Change() 

Select Case Address_21 
Case ""2306 Long Green Court" 
City_1.Value = "Valrico" 
Case ""3440 Oakcliff Road" 
City_1.Value = "Atlanta" 
Case "7015 East 14th Aveneue" 
City_1.Value = "Tampa" 
Case "1800 Taylor Drive" 
City_1.Value = "Gastonia" 
End Select 

End Sub 


"Brian" wrote:

> I am having trouble getting a text box to auto fill from a choice made from a 
> combo box.
> 
> Combo Box Name is Address_21
> Text Box Name is City_1
> 
> My code is as follows:
> 
> 'Auto Fill CES Spec Written Office
> 
> Private Sub Address_21_Change()
> 
>   Select Case Address_21
>      Case ""2306 Long Green Court" 
>          City_1 = "Valrico"
>      Case ""3440 Oakcliff Road" 
>          City_1 = "Atlanta"
>      Case "7015 East 14th Aveneue" 
>          City_1 = "Tampa"
>      Case "1800 Taylor Drive" 
>          City_1 = "Gastonia"
>   End Select
> 
> End Sub
> 
> Can someone help me figure out what I did incorrectly?
> 
> 
0
Utf
12/17/2009 2:58:01 PM
You might have one of the elements of the combobox spelled wrong.  I 
personally like to you the .ListIndex property instead to avoid this problem. 
 For example,

Private Sub Address_21_Change()

  Select Case Me.Address_21.ListIndex
     Case Is = 0: Me.City_1 = "Valrico"     '2306 Long Green Court
     Case Is = 1: Me.City_1 = "Atlanta"     '3440 Oakcliff Road
     Case Is = 2: Me.City_1 = "Tampa"       '7015 East 14th Aveneue
     Case Is = 3: Me.City_1 = "Gastonia"    '1800 Taylor Drive
  End Select

End Sub


Hope this helps!  If so, let me know, click "YES" below.
-- 
Cheers,
Ryan


"Brian" wrote:

> I am having trouble getting a text box to auto fill from a choice made from a 
> combo box.
> 
> Combo Box Name is Address_21
> Text Box Name is City_1
> 
> My code is as follows:
> 
> 'Auto Fill CES Spec Written Office
> 
> Private Sub Address_21_Change()
> 
>   Select Case Address_21
>      Case ""2306 Long Green Court" 
>          City_1 = "Valrico"
>      Case ""3440 Oakcliff Road" 
>          City_1 = "Atlanta"
>      Case "7015 East 14th Aveneue" 
>          City_1 = "Tampa"
>      Case "1800 Taylor Drive" 
>          City_1 = "Gastonia"
>   End Select
> 
> End Sub
> 
> Can someone help me figure out what I did incorrectly?
> 
> 
0
Utf
12/17/2009 2:59:01 PM
see if this change to your code helps:

Private Sub Address_21_Change()

    With Me

        Select Case .Address_21.Text

        Case "2306 Long Green Court"

            .City_1.Text = "Valrico"

        Case "3440 Oakcliff Road"

            .City_1.Text = "Atlanta"

        Case "7015 East 14th Aveneue"

            .City_1.Text = "Tampa"

        Case "1800 Taylor Drive"

            .City_1.Text = "Gastonia"

        End Select

    End With

End Sub
-- 
jb


"Brian" wrote:

> I am having trouble getting a text box to auto fill from a choice made from a 
> combo box.
> 
> Combo Box Name is Address_21
> Text Box Name is City_1
> 
> My code is as follows:
> 
> 'Auto Fill CES Spec Written Office
> 
> Private Sub Address_21_Change()
> 
>   Select Case Address_21
>      Case ""2306 Long Green Court" 
>          City_1 = "Valrico"
>      Case ""3440 Oakcliff Road" 
>          City_1 = "Atlanta"
>      Case "7015 East 14th Aveneue" 
>          City_1 = "Tampa"
>      Case "1800 Taylor Drive" 
>          City_1 = "Gastonia"
>   End Select
> 
> End Sub
> 
> Can someone help me figure out what I did incorrectly?
> 
> 
0
Utf
12/17/2009 3:05:01 PM
Works perfectly

Thanks

"Mike" wrote:

> Try this
> Private Sub Address_21_Change() 
> 
> Select Case Address_21 
> Case ""2306 Long Green Court" 
> City_1.Value = "Valrico" 
> Case ""3440 Oakcliff Road" 
> City_1.Value = "Atlanta" 
> Case "7015 East 14th Aveneue" 
> City_1.Value = "Tampa" 
> Case "1800 Taylor Drive" 
> City_1.Value = "Gastonia" 
> End Select 
> 
> End Sub 
> 
> 
> "Brian" wrote:
> 
> > I am having trouble getting a text box to auto fill from a choice made from a 
> > combo box.
> > 
> > Combo Box Name is Address_21
> > Text Box Name is City_1
> > 
> > My code is as follows:
> > 
> > 'Auto Fill CES Spec Written Office
> > 
> > Private Sub Address_21_Change()
> > 
> >   Select Case Address_21
> >      Case ""2306 Long Green Court" 
> >          City_1 = "Valrico"
> >      Case ""3440 Oakcliff Road" 
> >          City_1 = "Atlanta"
> >      Case "7015 East 14th Aveneue" 
> >          City_1 = "Tampa"
> >      Case "1800 Taylor Drive" 
> >          City_1 = "Gastonia"
> >   End Select
> > 
> > End Sub
> > 
> > Can someone help me figure out what I did incorrectly?
> > 
> > 
0
Utf
12/17/2009 7:28:01 PM
Reply:

Similar Artilces:

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Filter records that include certain text strings
I want to create customized reports where a user can enter text strings, then the program will search for all records that contain that text string and display them. The filter functions are exclusive. -- W Dean Welch On Jul 6, 6:34 pm, Dean Welch <wdeanwe...@bellsouth.net> wrote: > I want to create customized reports where a user can enter text strings, then > the program will search for all records that contain that text string and > display them. The filter functions are exclusive. > -- > W Dean Welch In the same field? Just pass a filter in the open event of the ...

Re: Update for Windows Vista (KB980248) -- No more fonts available!
cf. http://social.answers.microsoft.com/Forums/en-US/vistawu/thread/f7fa37ca-683c-4be1-b4ad-ab2dd1249399 PA Bear [MS MVP] wrote: > Are you running Vista SP1 or Vista SP2? Is Office 2010 installed? > > Was KB980248 offered & eventually installed via Windows Update or did you > (attempt to) install it manually? > > What anti-virus application or security suite is installed and is your > subscription current? What anti-spyware applications (other than > Defender)? > What third-party firewall (if any)? > > Has a(another) Norton or McAfee ap...

Incorrect Office Updates in Microsoft Update
Hello I recently did clean installs of XP and Win7 on 2 different PCs. Each PC was loaded with Office 2007. When I ran Microsoft Update after the Office installs, it shows I needed Office 2007 SP1, not SP2. I manually downloaded and installed Office 2007 SP2 and then checked Windows Updates for any needed patches. It still showed I needed Office 2007 SP1...even though I just installed SP2. Does anyone know how to 1) have Office SP2 show up as a needed update instead of SP1, and 2) how to configure Windows Update to show that no SP is needed after SP2 is installed? th...

Update Manager
Everytime I boot up my PC a small window pops up entitled "UPDATE MANAGER". It shows two progress bars, but nothing ever happens. Why is this window popping up every tiem I turn on the computer and how do I get it to stop? It never used to do this, it just statred lately. Thankyou. tony Because you installed something which features an update manager "Omaha Tony" <OmahaTony@discussions.microsoft.com> wrote in message news:A08659C0-1560-4E8C-8A1C-76A5B1DBB1F8@microsoft.com... > Everytime I boot up my PC a small window pops up entitled "UP...

How can I wrap text across merged cells?
I'm using Excel 2000. I have a set of merged cells A5- E5. I have several lines of text in the cells and I want them to wrap across the merged cells and it is not working. All I get is the first line of text showing and the rest is cut off. It works if the cells are not merged, but I really need to do it in my merged cells. Is there a way to this other than manually resizing the height of the row? Instead of merging cells, have you tried the "Center Across Selection" option ? The appearence is just about identical to what you would get using merged cells, although I d...

preview text disappears when email is opened
When someone responds to an my email, I can see what they have typed - until I open the message - then only my original message is viewable. The preview pane shows new message text when email arrives, hoever when the email is opened the preview text disappears - and I just see the earlier part of the message which I had sent. If I mark it as unread it does not return to the previous state. This seems to happen on email responses that people have received and answered from a webmail service not that they downloaded and opened in outlook on their PC OL'03 XP pro sp2 -- Respectfully,...

automatic office updates
Is there a way to configure a PC to receive Office Updates automatically like Windows Update? Thanks. Jeff Via MicrosoftUpdate as apposed to windowsupdate, and then configuring how you update. eg automatic or download and notify "J Miller" <jmiller@*nospam*pcsdps.org> wrote in message news:456db17c$0$81355$ae4e5890@news.nationwide.net... > Is there a way to configure a PC to receive Office Updates automatically > like Windows Update? > > Thanks. > > > Jeff > > Yes. Microsoft updates does this. What version of Office do you use? -- Diane...

does vista installed on virtual machine 2007 get wsus updates ?
It is searching for updates but it is not finding anything and saying that Windows is up to date. I have set the updates to install from the wsus server and assigned the updates to the same Vista virtual machine .. Thank you -- aconti ------------------------------------------------------------------------ aconti's Profile: http://forums.techarena.in/members/73272.htm View this thread: http://forums.techarena.in/active-directory/1290161.htm http://forums.techarena.in Hello aconti, If the machine is getting the correct GPO for the WSUS settings, check with rsop...

Outlook 2000 doesn't respond after downloading MS update!!
Luckily, my Outlook Express seems to work, but my Outlook 2000 will open, but if I click on a message or do Anything, it FREEZES. HELP!! I rely on calendar functions, other things. HOW could MS allow an update that screws up their own programs? I did restore - to no avail. I lent CD to someone, so can't do "repair." (Trying to get a hold of friend) Please help! I'm in process of job hunt & really need Outlook to function... Thanks, Sherry Did you recently apply SP3 for Office/Outlook 2000 and is Outlook 2000 configured in Internet Mail Only mode? If yes, you...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

if statement with text
I am trying to create an if statement where the string of text contains total, if yes then perform calculation base on that row and column. if no total in text leave blank help =IF(ISERROR(FIND("Total", A1)),"",-yourCalculation-) FIND returns #VALUE if "Total" is not in A1 Therefore, ISERROR returns False if "Total" is in A1 (i.e., it was found, no error) HTH, -- George Nicholson Remove 'Junk' from return address. "jerry" <jerry@discussions.microsoft.com> wrote in message news:E249057A-F6E4-45BF-929A-B2BB61C3A700@micro...

can't update office xp
I can't download the latest updates for office xp. I get a "hotfix" error. I also can't have microsoft check my computer for installed updates. it just tells me that my computer is unable to communicate with microsoft. >-----Original Message----- >I can't download the latest updates for office xp. I get >a "hotfix" error. I also can't have microsoft check my >computer for installed updates. it just tells me that my >computer is unable to communicate with microsoft. >. >guess no one has any reply to this, should I reload outloo...

Changing the Selection Fields in a Combo Box
Hi, I've created a Combo Box using the Controls Wizard. In creating the control, I specified that my unique value to be stored in the table is an ID. When the user views the selection options in the Combo Box, I'd like to have the ID field hidden and only see the text values, while still storing the ID in the appropriate column. This is probably a second question. Also, when the user views a form that has been populated, I'd like them to see the actual textual values on the form, as just the stored ID value. Is that possible to do with the one Combo Box or is that...

Contact looses Post Office Box field when synchronized to Outlook
When I sync contacts from CRM 4.0 to Outlook, the Post Office Box field from the contact gets empty. Anyone has the same issue? ...

Need help with update sql plus filter
I have the following update sql (copied from the query design view) UPDATE ListQry SET ListQry.ApprovalStatusID = [Forms]![OpeningForm]![Responsibility] WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null)); ApprovalStatusID is an integer OtherStatusID is an integer ListQry is the recordsource for my form. I would like to add the f...

convert text in excel to uppercase
Is it possible to convert all text in a workbook or on a spreadsheet to all uppercase? In VBA Editor Sub MakeUpper() Dim MySht As Worksheet, MyCell As Range For Each MySht In ThisWorkbook.Sheets For Each MyCell In MySht.UsedRange.Cells MyCell = UCase(MyCell) Next Next End Sub "elaine" wrote: > Is it possible to convert all text in a workbook or on a spreadsheet to all > uppercase? Hi see: http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany "elaine" <elaine@discussions.microsoft.com> s...

Excel link update question
I need to maintain an excel workbook which contains a lot of links to other workbooks. Since the linked workbooks change every week, I need to change all the links accordingly. For example, a cell with formula "='[aug_28.xls]sheet1' !A10" will be changed into "='[sep_4.xls]sheet1'!A10". I tried to do this with Find/Replace. However, the link is updated every time it is changed. The link updating takes a couple of seconds. So it may take a hour to finsh it for a workbook with thousands of links. Could anyone tell me how to shut down the link updating when...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

SP 1 & 2 Updates
I noticed that when I installed the SP 1 & 2 Updates, I could no longer get .zip, .doc, .htm, .exe attachments and anything like that (.jpg and .gif are no problem). Is there a way to enable attachments of other types to come through? I would like to install the Updates, but need to be able to receive attachments. Thanks for your help. ...

How do I lock a chart so it will not update?
That's the question. I have my data in Excel and the chart in Excel but not all the data cells are used. Everytimg I open the chart it wants to update and I want it to stay the same. Any ideas on how to lock the chart? Hi Just a few ideas: You could lock the cells that are shown in the chart. Or you could copy the cells and paste as values (assuming formulas were used that update when other cells change). -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Locking a Chart" wrote: > That's the question. I have my data in Excel and the chart in ...

List Box Point-to-Select (MouseMove)
I have a list box that shows files from my hard drive. I'd really like to have it select the item being pointed to as I move my mouse pointer over it. I have coded this and it works fine unless the list is scrolled. I need a way to offset my list index to account for how far down the list has been scrolled. I can't find a list box property that will allow me to determine where a scrolled list is positioned. Something giving me the list index of the first visible row would do the trick. I haven't been able to find anything so far. I've tried some VB code that uses the Send...

Microsoft Update 'left behinds'
Every time I download and install the latest Microsoft Update 'fixes' to XP-Pro, the file space taken up by XP-Pro grows and grows. While this will be an elementary question to many, is some of this XP-Pro file space 'growth' being caused by stuff that the 'fixes' leave behind and files that XP-Pro no longer needs? If so, can these 'left behind' files be gotten rid of so as to reclaim some HD space? Are there any pros and cons to deleting these 'left behind' files? Assuming that these 'left behind' files can be deleted, what i...

BofA Account not updating
My BofA credit card account is not updating in Money 2007 Deluxe. It has not updated since 16 May. Is anyone else having this problem? A call to MS has yielded no results... ...

Provide space in text
Where text and numbers are at different locations in different cells what formula could be used to suit this type of information. I want to be able to put a space between the main body of text on the left and give a space between the text and the start of any numbers are in the cell. Example 1: A1 = Abelia Edward Goucher2 litre Should be: Abelia Edward Goucher 2 litre Example 2: A2 = Acer campestre Nanum180stem 6-8 Should be: Acer campestre Nanum 180stem 6-8 Thankyou if can be of help. Pat If you double clcik in the cell with the data then you can move th blink line to were you wan...