Combo or Drop Boxes?Advice?Help?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Combo or Drop Boxes?Advice?Help?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    rejdus@hotmail.com
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I need this for my IT project.
    I have a spreadsheet with list of clients and its types. Names starts at B7 and by running macro i made new clients can be added to the row.
    What I am confused with now is that i have other spreadsheet wit invoices where heading is MAINCONTRACTOR and its begin at cell C4.
    What i am looking for is that i want to make combo box which will have on that invoice spreadsheet list of clients which is found in the previous clients spreadsheet,the ones already in and it should also be added automatically new clients when added so i can choose in a column whoever contractor i want.
    Anyone willing to help?
    Please

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-31 16:12, Mayk wrote:
    I need this for my IT project.
    I have a spreadsheet with list of clients and its types. Names starts at B7 and by running macro i made new clients can be added to the row.
    What I am confused with now is that i have other spreadsheet wit invoices where heading is MAINCONTRACTOR and its begin at cell C4.
    What i am looking for is that i want to make combo box which will have on that invoice spreadsheet list of clients which is found in the previous clients spreadsheet,the ones already in and it should also be added automatically new clients when added so i can choose in a column whoever contractor i want.
    Anyone willing to help?
    Please
    Lets say that the worksheet containing client names is called Data.

    Activate Insert|Name|Define.
    Enter EndRow as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =MATCH(REPT("z",40),Data!$B:$B)

    Activate Add.

    Enter Clients as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =OFFSET(Data!$B$7,0,0,EndRow-6,1)

    Activate OK.

    Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.

    Aladin





  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    rejdus@hotmail.com
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    On 2002-03-31 16:27, Aladin Akyurek wrote:
    On 2002-03-31 16:12, Mayk wrote:
    I need this for my IT project.
    I have a spreadsheet with list of clients and its types. Names starts at B7 and by running macro i made new clients can be added to the row.
    What I am confused with now is that i have other spreadsheet wit invoices where heading is MAINCONTRACTOR and its begin at cell C4.
    What i am looking for is that i want to make combo box which will have on that invoice spreadsheet list of clients which is found in the previous clients spreadsheet,the ones already in and it should also be added automatically new clients when added so i can choose in a column whoever contractor i want.
    Anyone willing to help?
    Please
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Lets say that the worksheet containing client names is called Data.

    Activate Insert|Name|Define.
    Enter EndRow as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =MATCH(REPT("z",40),Data!$B:$B)

    Activate Add.

    Enter Clients as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =OFFSET(Data!$B$7,0,0,EndRow-6,1)

    Activate OK.

    Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.

    Aladin
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Aladin thnx 4 help
    but i have few misunderstandings. Where should i type the formulas?macros box or formula box with = sign? i am not quite sure.
    Also "Activate Insert|Name|Define." i dont really know what i shoul do in this step?r u able to explain more?plz
    "Lets say that the worksheet containing client names is called Data."dont know wot dat is either.plz help a bit more if u get a time...
    Thank you

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    Mayk,

    My understanding is that you have the names of the clients in some worksheet. What is the name of this worksheet: Sheet1 or what?

    You seem to have a macro that enables you to add new client names to the already existing list of names. Right?

    You have also another worksheet that you use to set up an invoice. And, you want to be able to select from either a dropdown list or a ComboBox the name of the relevant client. Right?

    Addendum: Is this a school project, I mean, an assignment?

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-01 03:32 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    rejdus@hotmail.com
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Mayk,

    My understanding is that you have the names of the clients in some worksheet. What is the name of this worksheet: Sheet1 or what?

    You seem to have a macro that enables you to add new client names to the already existing list of names. Right?

    You have also another worksheet that you use to set up an invoice. And, you want to be able to select from either a dropdown list or a ComboBox the name of the relevant client. Right?

    Addendum: Is this a school project, I mean, an assignment?

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-01 03:32 ]
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Aladin

    sorry for not being online for a while and keep you waiting. Firstly yes ure right this is a project in excel.

    What Ive done is a made a seperate spreadsheet from the main one, so there are two spreadsheets:
    1/ As~project- main one
    2/DATABASE~4~COMBO- database one

    In DATABASE~4~COMBO spreadsheet i have a data of clients,it starts in a row B7.
    then I made a button which allows me to enter new client under lat one entered.
    What i need now is to make a combo box in As~project spreadsheet which will import clients names from DATABASE~4~COMBO spreadsheet.
    What I dunno is that i dont know where and maybe how do i enter this commands u gave it to me before. like the ones below.Plz if u can help or give me a piece of small advice.
    Thanx

    Activate Insert|Name|Define.
    Enter EndRow as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =MATCH(REPT("z",40),Data!$B:$B)

    Activate Add.

    Enter Clients as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =OFFSET(Data!$B$7,0,0,EndRow-6,1)

    Activate OK.

    Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-04 11:41, Mayk wrote:
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Mayk,

    My understanding is that you have the names of the clients in some worksheet. What is the name of this worksheet: Sheet1 or what?

    You seem to have a macro that enables you to add new client names to the already existing list of names. Right?

    You have also another worksheet that you use to set up an invoice. And, you want to be able to select from either a dropdown list or a ComboBox the name of the relevant client. Right?

    Addendum: Is this a school project, I mean, an assignment?

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-01 03:32 ]
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Aladin

    sorry for not being online for a while and keep you waiting. Firstly yes ure right this is a project in excel.

    What Ive done is a made a seperate spreadsheet from the main one, so there are two spreadsheets:
    1/ As~project- main one
    2/DATABASE~4~COMBO- database one

    In DATABASE~4~COMBO spreadsheet i have a data of clients,it starts in a row B7.
    then I made a button which allows me to enter new client under lat one entered.
    What i need now is to make a combo box in As~project spreadsheet which will import clients names from DATABASE~4~COMBO spreadsheet.
    What I dunno is that i dont know where and maybe how do i enter this commands u gave it to me before. like the ones below.Plz if u can help or give me a piece of small advice.
    Thanx

    Activate Insert|Name|Define.
    Enter EndRow as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =MATCH(REPT("z",40),Data!$B:$B)

    Activate Add.

    Enter Clients as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =OFFSET(Data!$B$7,0,0,EndRow-6,1)

    Activate OK.

    Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Activate Insert|Name|Define means:

    Go to the Menu bar of Excel, choose Insert, choose Name in the Insert menu, and choose Define in the Name menu. After this, you end up in a window. Then carry out the rest of the instructions.



  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    rejdus@hotmail.com
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Activate Insert|Name|Define.
    Enter EndRow as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =MATCH(REPT("z",40),Data!$B:$B)

    Activate Add.

    Enter Clients as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =OFFSET(Data!$B$7,0,0,EndRow-6,1)

    Activate OK.

    Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Aladin i done what u ve told me however when i highlite the combobox and
    Activate Insert|Name|Define then i typed all the formulas u told me to.
    However at the end at last step when i activate OK and come back to invoice sheet where combo box was ,there is no data there?
    am i doing something wrong?

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-05 03:15, Mayk wrote:

    Activate Insert|Name|Define.
    Enter EndRow as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =MATCH(REPT("z",40),Data!$B:$B)

    Activate Add.

    Enter Clients as name in the Names in Workbook box.
    Enter the following formula in the Refers to box:

    =OFFSET(Data!$B$7,0,0,EndRow-6,1)

    Activate OK.

    Now, you should be able to use Clients as Source in data validation or as List range in a ComboBox.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Aladin i done what u ve told me however when i highlite the combobox and
    Activate Insert|Name|Define then i typed all the formulas u told me to.
    However at the end at last step when i activate OK and come back to invoice sheet where combo box was ,there is no data there?
    am i doing something wrong?
    Aladin's answer is fine for 1 Workbook but won't work from one to another.

    In case your wondering though, by follow Aladin's instructions you've created an Dynamic range, in which you list is housed.

    The two ways of using this list (but only within the same Workbook) are:

    1) Select the cell you want your list to be in,
    Goto Data>Validation which brings up a dialogue box,
    Click the Allow Combobox and select List.
    Now, in the Source box type =Clients.
    THis method is for a drop down box which will reveal itself when you select that cell.

    2) For the Combobox, if your using the one from the Controls Toolbox list (it doesn't work ofr the Forms controls),
    in design mode, Right Click on the button select Properties,
    in the box next ListFill type =Clients.

    Sorry I can't proporly answer your question, what (I think) you need is some VBA help, something I'm no Novice at.


    "Have a good time......all the time"
    Ian Mac

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Just thought actually, in your Invoice book on a separate sheet called Data you could link the cells to the Client Workbook and follow Aladin's instruction in the Invoice Book instead.
    "Have a good time......all the time"
    Ian Mac

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com