Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

ComboBox

I know this is a simple task, but i am very weak with comboboxes on my userform, i can additems by inserting them into the code. but how do i add these items from an excel worksheet.  For example i need to add the data from a sheet labeled qcdata4 cell ag26,ag30,ag44,ag52. I know this has to be done in a userform intialize event but do not know the code. any help would be greatly appreciated.
0
stormhunter2000
Asked:
stormhunter2000
  • 3
1 Solution
 
killer5Commented:
*** I ASSUME YOU ONLY HAVE 4 ITEMS IN COMBOBOX ALL THE TIME
*** This may not be the exact solution but it will get you
*** started


Dim EApp As Excel.Application
Dim ESheet As Excel.Worksheet
Dim EBook As Excel.Workbook

Set EApp = CreateObject("Excel.Application")
EApp.Visible = False
EApp.Workbooks.Add
Set EBook = EApp.ActiveWorkbook
Set ESheet = EBook.Worksheets.ActiveSheet

Combo1.ListIndex = 0
ESheet.Range("AG26").Value = Combo1.Text
Combo1.ListIndex = 1
ESheet.Range("AG30").Value = Combo1.Text
Combo1.ListIndex = 2
ESheet.Range("AG44").Value = Combo1.Text
Combo1.ListIndex = 3
ESheet.Range("AG52").Value = Combo1.Text

EBook.SaveAs ("C:\Test.xls")
EApp.Application.Quit
Set EApp = Nothing
Set ESheet = Nothing
Set EBook = Nothing
0
 
killer5Commented:
make sure you reference Excel Object.
1. click Project from menu
2. click References
3. Look for Excel Object Library (any version) then click OK
0
 
killer5Commented:
there is an error in the code.. sorry.

just replace the
Set ESheet = EBook.WorkSheets.ActiveSheet
with
Set ESheet = EBook.ActiveSheet

thanks!
0
 
DanRollinsCommented:
Hi stormhunter2000,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept killer5's comment(s) as an answer.

stormhunter2000, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now