Populating Combo boxes using For...Next Loop

[cutegwapings]

I'm a newly registered user of MrExcel.com, and I'm glad to be here.


I just want to know how to populate combo boxes using for...next loop.

I'm using VB in excel, and I'm getting err message "Sub or Function not defined" when I tried to run the program.

Here's my code.

Private Sub Worksheet_Activate()

Dim I As Integer

For I = 1 To 5
ComboBox(I).Clear
ComboBox(I).AddItem "N/A"
ComboBox(I).AddItem "YES"
ComboBox(I).AddItem "NO"
ComboBox(I).Text = ComboBox1.List(0)
Next I

End Sub

Thank you so much!

 

[cutegwapings]

Thanks for all your help!

This forum is very helpful. Response is quick. Even simple and dumb questions are answered in a friendly manner.

Thanks erik.van.geit


I would like to buy a VBA excel book, which one do you recommend?


Thanks!

 

[erik.van.geit]

you're very WELCOME

about books: sorry, can't help much there: I've never read a book about VBA, just checked once rapidly a "tryout"book

do a search on the board (link on top of page) or use google
http://www.google.com/advanced_search?q=site:MrExcel.com

greetings form Belgium,
Erik

 

[cutegwapings]

Another question

FORMULA:

If Repnames.Text = Sheets("Audit Summary").Range("B5:B80").Text Then
MsgBox "This tech has been audited, go to Audit Summary tab and delete the record first.", vbOKOnly
Exit Sub


Note: Repnames is a combo box.

I want the program to compare the name chosen from the combo box and compare it to the data already saved on the other sheet named "audit summary" to avoid data redundancy.

What's the correct formula? I'm not getting any error message but the MSGBOX is not appearing.

Thanks!

 

[erik.van.geit]

is this a merged cell: B5:B80 ?
insert a line:

MsgBox Sheets("Audit Summary").Range("B5:B80").Text

to clarify for yourself what the text in the cells reads

any help with this ?

 

[cutegwapings]

Thanks again for your reply erik.

The data on those cells were not merged. Those are the list of names from B5 to B80.

I have a seperate program on another sheet named "extractor". There's a combobox named "repnames" on the "extractor" sheet where you have to choose one of the names.

When I run the program in "extractor" it will paste the data on the "audit summary" sheet. What I would like to happen is for the program to look at the list of names from B5 to B80 on the "audit summary" sheet and compare it to the name that's inputed on the "repnames" combo box from the "extractor" sheet.

A message should appear and say "The name you're trying to save is already on the database."

Thanks again for your help friend...

 

[erik.van.geit]

look at the list of names from B5 to B80 on the "audit summary" sheet and compare it to the name that's inputed

you can use FIND
the helpfiles are very good

Sheets("Audit Summary").Range("B5:B80").Find(Repnames.Text, ... see the other arguments in the helpfiles
CAVEAT: when using FIND it is recommended to fill in all relevant arguments: if you don't Excel will remember the settings from your previous FIND, be it manually or by code: this would leave you with unexpected results

 

[cutegwapings]

hello again..

Thanks for all your help erik. It seems like you're the only one who's willing to help me here, thanks for that...

I tried to learn on my own but I'm really a novice on programming. I wasn't still able to solve my last question.

I'd really appreciate if you could give me the exact code on how to use find.


Thanks!

 

[agihcam]

Hi -
try ( untested )

Sub sample()
With Sheets("audit summary").Range("b5:b80")
    Set c = .Find(Sheets("extractor").repnames.Text, , , xlWhole)
        If Not c Is Nothing Then
            MsgBox "This tech has been audited, go to Audit Summary tab and delete the record first.", vbCritical + vbOKOnly, "repnames already exists"
        End If
End With
End Sub

 

[erik.van.geit]

Re: hello again..

Thanks for all your help erik. It seems like you're the only one who's willing to help me here, thanks for that...

I tried to learn on my own but I'm really a novice on programming. I wasn't still able to solve my last question.

I'd really appreciate if you could give me the exact code on how to use find.

I can not agree with your first remark. There are a lot of people willing to help here.
for example agihcam

I understand you are a novice: we have all been there. When trying to use code, "play" with it. This means:
1. forget your own project
2. replicate the code from the helpfiles
3. execute it
4. read the documentation
5. change some arguments, sheetnames, .... (this is the real PLAYING )
6. only then go back to your project and will look a lot easier

 

[cutegwapings]

hello again...

Alright... THanks!

Is there a way to enable the use of tab in excel? So that the user won't use the mouse to click on every fields?