Macros with buttons, spin buttons, scroll buttons, etc.

[qqbbppdd]

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?
Private Sub Worksheet_Change(ByVal Target As Range) If Range("A10") <> "" Th - Pastebin.com <--- worksheet VBA code.

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...
Sub Spausdinti() ' ' Spausdinti Macro ' ' ActiveWindow.SelectedSheets - Pastebin.com <--- printer icon VBA code.

Adding my worksheet: http://www12.zippyshare.com/v/978311/file.html

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

 

[Worf]

Hello and welcome to the Board
Let’s do this one item at a time, because it’s no small project. I hope you are not on a tight deadline…
One possible solution for the first item is:

• Apply the filter on the original table at Prekių sąrašas sheet, copying the result to another location
• Change the formulas at Pagrindinis sheet, range F13:M32, so that they refer to the filter result
• When done, restore the original formulas

Would you like me to write code for this?

Laukiu Jūsų atsakymo!

 

[qqbbppdd]

Thank you for your reply, Worf. I was hoping I could finish this task until this Tuesday (17/12/13), but the deadline is till New Year evening. I've actually simplified my workbook like this:
1) Made a long table with basic Advanced Filter, but my macro does the following: Sub Pagrindinis_IðvalytiFiltrà() ' ' Pagrindinis_IðvalytiFiltrà Macro ' - Pastebin.com but the main problem is if that table is not filtered, I get an error with debugger selection. If it's filtered, everything works just fine. Any solutions to the code which can fix that?
2) Doesn't matter anymore, since I dropped price filter. Couldn't figure out how to filter the price between 2 given numbers.
3) and 4) Made an icon for every row that has data and now I need to be able to copy an entire row and paste it to another sheet table. But the trick is that if there is any data in that 2nd table (1st row), the macro should paste everything into 2nd row. And if 2nd row has data, macro should paste everything into 3rd row and so on... If row 10 is filled, an error would appear saying that shopping cart is full (optional).
5) Really doesn't matter, optional, i guess. It is fine with spin buttons being shown.
6) Figured it out, don't need this anymore.

Hope I simplified my tasks enough to make this possible!

Best regards,
David

 

[Worf]

Hi David

1)I‘m not sure if I understood. Are you working with the whole list now(easier) or still with the scrollable range(more elegant, but also more complex)?

2) See this page,section „Extract Items in a Range”, on how to filter for a specified range of values. Are you going to reactivate this item?

http://www.contextures.com/xladvfilter01.html

 

[qqbbppdd]

Thanks for your reply!

1) I've dropped =offset table and scrollable range and made just a table with >100 rows. When it's filtered, it looks OK, but when the filter is cleared, the table looks kinda messy. What I've done just copied from another sheet with full data and pasted it for the filter function. If you're not following me here I'll upload my workbook so you can check it out.
2) What I've been trying to do was to filter between price, i.e. between $1,000 and $4,000, but with formulas instead of raw numbers. Couldn't do that so I've dropped this criteria. Now this filter looks like it's missing an important filtering criteria, but I can live with that - it wasn't mandatory...

I am pretty much done but still lacking 3) and 4) tasks from my previous post. When simplified this workbook looks not so clean and with less functionality but at least I've done it, despite the fact that I had no experience with macros until this November.

As promised - attaching updated workbook: Zippyshare.com - Kursinis_1_v3 - Copy - Copy.xlsm

 

[Worf]

Hi
Here are two macros:
- your multiple criteria code
- an example on how to filter for a numeric range of prices.

Now I'll move on to the other items...

Sub Pagrindinis_filtruoti()
'
    
    Sheets("Pagrindinis").Range("B16:U132").AdvancedFilter Action:=xlFilterInPlace, criteriarange:= _
        Sheets("Papildoma informacija").Range("K1:P2"), Unique:=False
    Application.Goto Reference:="R1C1"
End Sub


Sub Filter_by_Price_Range()
Dim ws As Worksheet
Set ws = Sheets("Papildoma informacija")
ws.Range("k12") = "Kaina"
ws.Range("L12") = "Kaina"
ws.Range("k13") = ">4000"
ws.Range("L13") = "<5000"
Sheets("Pagrindinis").Range("b16:u132").AdvancedFilter Action:=xlFilterInPlace, criteriarange:= _
ws.Range("K12:L13"), Unique:=False
End Sub

 

[qqbbppdd]

As always, thank you for your reply!

Unfortunately I've already got macro for multiple criteria filtering but I had to drop price unless there is a way to connect these two macros that you posted into one? I believe that I cannot assign two macros to one button...

 

[Worf]

If you set up the criteria range as follows, the price filter can be included:

Papildoma informacija

*	K	L	M	N	O	P	Q	R
1	Gamintojas	Procesorius	Darbine atmintis	Diskinio kaupiklio talpa	Ekrano istrižaine	Vaizdo plokšte	Kaina	Kaina
2	Apple	i7	*	*	*	Intel	>5000	<6000

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:70px;"><col style="width:78px;"><col style="width:105px;"><col style="width:140px;"><col style="width:105px;"><col style="width:95px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sub Pagrindinis_filtruoti()
    
    Sheets("Pagrindinis").Range("B16:U132").AdvancedFilter Action:=xlFilterInPlace, criteriarange:= _
        Sheets("Papildoma informacija").Range("K1:R2"), Unique:=False
    Application.Goto Reference:="R1C1"
End Sub

 

[qqbbppdd]

I've managed to achieve price filtering without macro with formula, so i guess price filtering is out of the question now. Thank you yet again for your precious time you are spending in order to help me out with my workbook!

 

[Worf]

Hi
Here is an example on how to manage the shopping cart:

- run the AddLinks routine to create the necessary hyperlinks
- paste the event handler code on the sheet module
- column X at Pagrindinis sheet is being used, you should hide it so people don't click its cells

Sub AddLinks()


Dim sh As Shape


For Each sh In Sheets("Pagrindinis").Shapes
    If sh.TopLeftCell.Column = 22 Then Sheets("Pagrindinis").Hyperlinks.Add _
    anchor:=sh, Address:="", SubAddress:=sh.TopLeftCell.Offset(, 2).Address
Next


End Sub

Dim r%


' this code goes at Pagrindinis sheet module


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Column = 24 Then
    r = Sheets("Cart").Range("b" & Rows.Count).End(xlUp).Row + 1
    If r > 12 Then
        MsgBox "Your shopping cart is full!", vbCritical, "The Notebook Shop"
        Target.Offset(, 2).Activate
        Exit Sub
    End If
    Sheets("Cart").Range("b" & r & ":u" & r).Value = Me.Range("b" & Target.Row & ":u" & Target.Row).Value
    Target.Offset(, 2).Activate
End If


End Sub