PDA

View Full Version : Macros with buttons, spin buttons, scroll buttons, etc.



qqbbppdd
12-12-2013, 06:32 AM
Hello folks, new member here looking for your wisdom to get my information technology exam done!

First things first, I'd like to apologize for my non-fluent English.

Now for my problems:
1) I have a table with >100 entries that is scrollable using Form Controls Scroll Bar. Also, I have a button called 'Filter' which is supposed to work like this: from a data validation list I can select laptop manufacturer, laptops processor, video card, memory, disk drive, price, etc. and when I assign a macro to that button using advanced filter, it filters only those records that I can see. Lets say I have 25 Asus laptops in my list, but table is showing only 20 of them, so advanced filter will filter only those 20 records with multiple criteria. Is there a solution that it would filter all those records including those that are at the end of the table and are not visible?


2) It would be great if you guys could help me to make a filter for that same button from 1) question, that could filter price from 999 to 9599.

3) Here comes a really tough one. In the same table I've made a shopping cart icon which should work like this: table has 20 records (with scrollable bar) and 20 icons. If I click on an icon it would copy required information to the other sheet. I've made it to do only for the first record, but how could I make it that it would copy those records sliding down the table? Lets say I need to copy 31st record, so I scroll down by 11 points and click the shopping cart icon. It should copy exactly 31st record in that table, but in my case it copies 20th record, since the button was made to copy 20th line...

4) Just like in 3) question the same icon should work like this: in the next sheet is a table where I want to copy all my records from previous table. If row 10 is not filled with data, it should copy the data from previous table to the 1st row. If 1st row has data, it should copy data to the next row, until 10th row is filled. Any ideas? Would appreciate it ALOT!

5) I've made a spin button disappear based on cell value. If a table does not have data in 1st row, spin button in 1st row which calculates quantity disappears (becomes invisible). Is there a way to modify the code I'm attaching to hide other 9 spin buttons according to their row data? If row 2 does not have any data, spin button 2 would become invisible and so on? Adding my worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A10") <> "" Then
Worksheets("Krepšelis").Shapes("Spinner 13").Visible = True
Else
Worksheets("Krepšelis").Shapes("Spinner 13").Visible = False
End If
End Sub


6) Will this code for a printer icon work? It has to print a page where that icon is. Cannot test it, since I don't have a printer... Adding printer icon code:

Sub Spausdinti()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub


Once again, any help would be appreciated,
Best regards,
David.

Aflatoon
12-12-2013, 07:02 AM
Also posted:
http://www.ozgrid.com/forum/showthread.php?t=184744
http://www.excelforum.com/excel-programming-vba-macros/974921-macros-with-buttons-spin-buttons-scroll-buttons-etc.html
http://www.mrexcel.com/forum/excel-questions/744632-macros-buttons-spin-buttons-scroll-buttons-etc.html
Possibly more. ;)

qqbbppdd
12-12-2013, 07:28 AM
Well, yes. Actually I've noticed, I believe, only in ozgrid (may be wrong) that it requires links to other forums if the same thread is made there. Since I've not seen any notifications here, I did not posted links...

Aflatoon
12-12-2013, 07:35 AM
Every Excel forum I know has a similar rule, even if some do not enforce it as strictly as others. It is courteous, regardless of the rules, to provide the links.

qqbbppdd
12-12-2013, 07:44 AM
Well yes, can't argue with the rules. To make up for it I'll add links to those threads when there would not be an error for uploading many links for new threads!