Display print buttons in UserForm

[john55]

hi guys,

I tried to solve a problem, I could not! Please help me to solve it!
I have a file with many sheets, in column X are the items, in column Y is a print buton (it prints 2,3 or 4 sheets), in column Z is another print button (it prints 1 or 2 sheets).
When we want to print we go up and down for a specific item. I want to have a UserForm with a ComboBox (from this one is selecting the item I want to print) and the print buttons to "appear" in UserForm.
(in the item's column are some gaps, empty rows, intentionally left, I do not know how to avoid "the gaps" without disrupting data)
I tried another method for printing using selecting the sheets, it's a good one but we have very different names for sheets so it's a little bit complicated to guess what sheets you need..!

Any help is really appreciated!

[royUK]

Hi John

I'm not sure if this is what you want, it uses 3 buttons in Frame 1 & 1 in Frame 2. You could probably use one button in frame 1 & simply change it's caption when the combobox changes[


ATTACH]138410[/ATTACH]

[john55]

hi Roy,
Thank you very much for your solution. I think we are close for what I want to get!
some comments:
1.I use a sList for combobox1 because we have a lot if items, most of them are there in column X, but some items are not, I add them if there are data to import and then the print buttons appear in column Y and Z. I think I can not use the array method to populate the combobox because I do not know all the items (the new ones) to be printed.

2.You use Select case for the second print button, I think I understand yr solution, to add the button name...it will be a loong list of cases! and here is the same problem with new items added.

for example:
I add a new item, somewhere at the bottom under the known items, i.e 1965.
so, 1965 is added in column X, in Y and Z columns will appear the corespondent print buttons with 1965 on the first print button. the code for print uses specific sheets.

if I delete 1965 , the print buttons disappear and I can add a new item...77789, the buttons appear again and I can print its sheets.
or if i need to add more I added one after the other.
This is the reason to have both print buttons for each item selected.

hope it does make sense

[royUK]

The blank rows make it impossible to use the ListIndex property of the combobox as I have. Maybe it could be based on the value selected in the combobox

[john55]

yes, it must be based on the value selected in the combobox. ok, I can "ignore" the blank rows....for user it's easier to select a value from combobox and have the buttons in the right side of it!

[royUK]

Here's a couple of ideas


_Display_Print.xls

[john55]

Thank you very much for your help, Roy!
I think I'll take the second solution because we use the second print button "azx" when we make update only and it prints usually one (or max 2 sheets). so user can search for and to do it!

Tmw I test it at office!

I feel embarrassed, I know it seems silly to ask but how the code "knows" what sheets should be printed when an item is selected?
(behind of each print button I have the code....)

Please Login or Register  to view this content.

I added the file to see how I add items and the buttons take the name...

[royUK]

I've assumed that all the sheets are similarly named except for 123, etc. This is taken as a string from the combobox & the shhet name built up

Please Login or Register  to view this content.

It creates
[code]
Set ws = Sheets(sCombo & "A") ' please tell me what "A" is/means...!

[john55]

I am sorry! no, the sheets have different names. I thought that the print button "comes" into userform and hitting the button the sheets are printed using the code what is in original Print Button.

[john55]

.......
bump

[royUK]

Are names just given randomly to sheets?

[john55]

yes, randomly. some of them could be easily recognized by item number but others has nothing to do with it.
this is the reason I'd like to have the button(s) into UserForm when I select the item.

[royUK]

You call maybe call the sheet's button click event, based on the selection made