Drop down in userform

[timarcarze]

Hi Forum,

I'm coding my first userform, but having a few probs, that would appreciate your assistance. Have attached sample for demonstration.

Basically, upon click of "Populate Scorecard" button (e.g. on Company X worksheet), would like user to complete the Userform and based on their response, it would add to the appropriate "Month" (selected from the Userform Drop down) in the Scorecard worksheet.

I have started an IF statement for "Jan-08" and was going to copy and paste code for each of the months, e.g "Feb 08, Mar-08 etc", but thought their would be an easier way based on Loops or Call statement. Just not sure how to do this? Can anyone assist with getting me started?

I'm also not sure how to add the code for "Buyer Comments" and "Month" to be added to seperate worksheet (e.g. Company X), if Company X was selected.

Note that i will create a hide sheet statement, so user can select which Company (e.g. X or Y) to select from. The code would then need to correspond with that companies Scorcard worksheet.

Hope someone can help.
Tim

[rylo]

Tim

1) I don't see where you are nominating the output sheet, but say you have another textbox where you enter the sheet name. You could then do something like

Please Login or Register  to view this content.

2) For the output, then do something like this to determine the output column in the nominated sheet

Please Login or Register  to view this content.

Then you can use the cells approach to nominating the output cell for the data.

HTH

rylo

[timarcarze]

Hi Rylo,

Thanks for response, but not sure I understand exactly what you mean.

Here is the Hide/Show worksheet userform code (that I excluded in original attachment):

Please Login or Register  to view this content.

I've also updated the Month form code, so the form correctly inputs the data into appropriate month, code used as follows (displayed Jan and Feb only):

Please Login or Register  to view this content.

My queries still remain:
(1) is there an easier way to do this?
(2a) Given my real file has many more worksheets, I need this code to correspond to the sheet selected - as per above Hide/Show form. E.g. the Hide/Show code would select from Company X, Company Y worksheet, etc. If Company X worksheet is selected, then most the data as per Month Form Code would be populated within "Scorecard X" worksheet. However I need to determine the code for below 2b.

(2b)How do I arrange for form to update Selected Sheet (e.g. such as Scorecard X in original attachment) and then copy that "month" (selected in userform) and populate in Company X worksheet (cell "11") including "Buyer Comments" in Company X worksheet (cell "A16").

Hope this gives more detail.
Thanks
Tim

[rylo]

Tim

In the code you have the nominated sheet name from combobox1.text from the cmdHide_click() code. So use that to set the sheet.

Then when you have the sheet set, you can use the match code I gave you to find the position in row 54 that has the month you have selected. This is a column for the output. So with your if statement code below

Please Login or Register  to view this content.

would become something like

Please Login or Register  to view this content.

You don't need IF statements as you will have nominated the output sheet, and you have the initial point for the output to take place. You could offset from that cell position for the other items in the same way you have done in this code.

Build a small test example to see how it works before trying to do the main item of work.

rylo

[timarcarze]

Hi Rylo,

I have tried your suggestion and used the following code to set up the original file (have not attached as was too large). But here is the code i used based on your suggestions - Not working properly, can you suggest changes?

Code:

Please Login or Register  to view this content.

Thanks
T

[rylo]

Hi

Can you expand on "Not working properly"? Maybe update your example file with the code etc, advise what inputs to use and explain where the output is failing, or the code stops and reasons.....


rylo

[timarcarze]

Rylo,

Ok, removed some of formulas to decrease size of file, see attached.

On Company X worksheet, click button, and populate form as follows:

Feb-08
Difot
5
5
5
5
5
Quality
0
0
0
0
0
0
Cust. Service
9
9
9
9
9
Mgmt Reporting
Yes

Add Record

The following error msg is displayed "Debug 9, Subscript out of range" and refers to:

Please Login or Register  to view this content.

What I would like to happen is as follows, button would be on Company X, Company y, etc.
If Company X button was selected, the following code would be inputed within Scorecard X"" worksheet (if Company Y button was selected, then inputed within Scorecard Y):

Please Login or Register  to view this content.

However the part I can't do is Copy and Paste the "Month" and "Buyer Comments" selected in the userform and enter within Company X as opposed to Scoredcard X as above code.

Hope that makes sense??
T

[rylo]

Hi

where is the variable combobox1 filled, and what is it? Or should this be a combobox from the form? If so where is it?

rylo

[timarcarze]

Hi

ComboBox1 is the "Select Worksheet" Combo Box in frmHide. The list will contain both "Company X" and "Scorecard X", however the code should be based on the user selecting Company X, where most the data would be inputed in Scorecard X and only Month and Buyer Comments to be inputed within Company X.

If this is too difficult, perhaps we should include a similar combobox above Month combo box within the frmBuyer userform?

Tim

[rylo]

Tim

As you will be pushing a button on a sheet, then how about a change like this. I've only updated the start of the pasting options. Note that you don't have to activate the output sheet to do the pasting. It is actually better if you don't.

Please Login or Register  to view this content.

See if that makes sense and you can expand to cover all the output.

rylo

[timarcarze]

Hi Rylo,

Thanks for the advice. I've tried the updated code - see attached version, however once I select a month from the drop-down and input sample data, I receive a Run-time Error 9, Subscript Error on following code

Please Login or Register  to view this content.

Do you know what I'm doing wrong? I don't think it likes range G9?

Thanks
Tim

[rylo]

Tim

How are you raising the form frmBuyer? I would have thought it would be from the button on sheet say, Company X, but that button isn't attached to anything.

rylo

[timarcarze]

Hi Rylo,

Oh, I originally had the form linked via Module 1, but have changed it within the Sheet 2, and seems to work:

Please Login or Register  to view this content.

When I enter sample data, it appears to correctly copy the data within cells (E.g. select Feb-08) C55-C59 Scorecard X, however the next Offset code for inputting the Quality data does not paste correctly, eg. currently coded as follows:

Please Login or Register  to view this content.

Do you know what I'm doing wrong?

Thanks
T

[rylo]

Tim

OutCell is a reference to a fixed point, so your offsets are all going to the same place. In the block below

Please Login or Register  to view this content.

all the output is going to the cell immediately below OutCell, so it will end up with only the text from txtNil as the final value.

Does that clear it up???

rylo

[timarcarze]

Thanks Rylo, I understand.

[rylo]

Tim

If this clears things up, can you please mark the post as solved.

rylo

[timarcarze]

Hi Rylo,

I'm made a couple of changes and was wondering if you could help with a vlookup function on this.

Cell A16 contains the vlookup. Basically, I want to enter the Buyer Comment from Scorecard X Range G53:R53, and return based on the month selected in Company X G11.

E.g. A16 should return "Timarcarze", when Dec-08 is selected.

Thanks
Tim

P.S - see updated attachment

[rylo]

Hi

Try

Please Login or Register  to view this content.

rylo

[timarcarze]

Ahhh, horizontal lookup of course! Thanks