Programming using VBA using combo box

**deanJ** · 10-22-2009, 12:17 PM

Hi all,

I am having a few problems tryijng to amend the code so that it only uses the text box and combo box below.

Unfortunately I am having a few problems. I am new and have amended this spreadsheet / code from another user on this site.

Would you please be able to assist me?

**jaslake** · 10-22-2009, 04:26 PM

Hi DeanJ
What is it that you want to do? The only issue I see from looking at your book is that you have a ComboBox sitting there not doing anything. That can be eliminated by going into Developer Mode, right click the ComboBox and select Cut.
Your code seems to work fine; what problems are you having?
John

**jaslake** · 10-23-2009, 11:48 PM

Hi DeanJ
The attached worksheet/procedure gets rid of ComboBox2. You indicate you

Unfortunately I am having a few problems

but you give no indication what your problems are. It's difficult to help solve issues if one has no idea what they are.
John

**deanJ** · 10-24-2009, 12:15 PM

Thanks this seems to have resolved the problems I was experiencing. I was amending the code another user had posted. The code was rather useful in another workbook I created but I was unable to amend the code so I could get rid of the second combo box. When I tried to delete it I was just coming up with a run time error.

Thanks so much and if you have time can you let me know what portion of the vba your removed?

Many thanks once again :o)

**deanJ** · 10-24-2009, 12:35 PM

Hi again,

I was wondering if we could make this into a form perhaps. Once the text box has part of the text and the combo box the rest, once the user selects the actual address from the combo box, in the form, would it be possible to have the text pasted in cell j4 perhaps?

**jaslake** · 10-24-2009, 01:41 PM

Hi deanJ
Regarding your question

can you let me know what portion of the vba your removed

I changed this code

Private Sub TextBox1_Change()
  Call CB_Init(TextBox1.Value, ComboBox2.Value)
End Sub

to this

Private Sub TextBox1_Change()
  Call CB_Init(TextBox1.Value, ComboBox1.Value)
End Sub

Regarding this question

I was wondering if we could make this into a form perhaps. Once the text box has part of the text and the combo box the rest, once the user selects the actual address from the combo box, in the form, would it be possible to have the text pasted in cell j4 perhaps?

Instead of pasting to j2, you want the value pasted to j4? Change this line of code from J2 to J4

Private Sub ComboBox1_Change()
  Worksheets("view").Range("J2") = ComboBox1.Value
End Sub

Are you asking if worksheet (View) could call a user form from which the macros would be run? If that's what you're asking, I'd assume that can be done. I'd need to look at it. What happens to worksheet (View) then, is it an empty sheet except for having a value parked in J4?
Perhaps you could expand on your concept a little further and at the same time, let me know the answer to my above questions.
John

**deanJ** · 10-24-2009, 09:16 PM

Thanks so much for your response. I am learning lots all the time and everyone including yourself has been so helpful.

The text box allows the user to type part of the word and select the whole address from the combo box below.

My main query was this... Instead of having the half portion of text constantly displayed I was wondering if maybe a button could be selected say 'choose address2. This would open a form of which a portion of the address could be enetered and the full address selected from the combo box below. Once the user selects the complete address they could push ok, clear, or cancel.

Once they push ok this would insert the complete address into a cell, say J2.

I know this is probably an in depth query and would require quite a bit of work but as I said I am a new user of VBA and would appreciate any assistance given.

I have researched quite a bit and have not come up with anything that would give me this level of funtionality.

I believe the end result would be a good tool for the end user.

Thanks once again and I look forward to hearing from you.

**jaslake** · 10-25-2009, 02:20 PM

Hi deanJ
You've sort of lost me in describing what you want to do so, I've attached what I have. The attached does essentially what the previous version did but uses a UserForm instead. You'll need to describe what it is you want changed. Tell me what it does now and what it is you would like it to do.
Let me know. John