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]

Very strange indeed. I tested with Excel 2003 today and worked like a charm. What Excel version are you using? Are you enabling macros when opening the workbook?
Are the hyperlinks there? I uploaded a turn-key book, ready to use.

 

[qqbbppdd]

My Excel version is 2013 and I've enabled editing and macro usage when I opened your workbook. The hyperlinks seems to be in place, just in case ran hyperlinks macro once more, still the same result...

 

[Worf]

I must go offline now, but will return tomorrow, my Internet connection allowing.
Will think of something in the meantime...

 

[qqbbppdd]

OK, so I've played with your code a little bit and looks like everything is now working as intended. A little question: I've got 10 spin buttons (Form Control) and was wondering is there a possibility to make them invisible if B11 is empty, i.e. 1st spinner invisible if B11 has no data, 2nd spinner invisible if B12 has no data and so on and if there is any kind of data spinner would be visible. Been working with this code: http://pastebin.com/raw.php?i=cjLiGKHA and it does make 1st spinner invisible but not the others. Any solutions?

 

[Worf]

Hi
Below are two macros:
o ShNames displays information concerning shape names and types;
o The event handler will make the controls appear and disappear; it assumes spin buttons named Spinner 4 through 13, positioned on rows 11 through 20.

Option Explicit
Dim s As Shape
' this code goes at Krepšelis sheet module


Sub ShNames()
Dim st$
st = ""
For Each s In Me.Shapes
    If s.Type = msoFormControl Then _
    st = st & s.Name & "  Type = " & CStr(s.FormControlType) & vbLf
Next
MsgBox st, vbInformation, "Control names and types"


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b11:b20")) Is Nothing Then
    Set s = Me.Shapes("Spinner " & CStr(Target.Row - 7))
    If Target.Value = "" Then
        s.Visible = msoFalse
    Else
        s.Visible = msoTrue
    End If
End If
End Sub

 

[qqbbppdd]

And for the last question: I have this macro http://pastebin.com/raw.php?i=b87vVyYs and it clears the filter if it was created with advanced filter and clears filter table (clear contents), but I get an error whenever I click that button when there is no filter made. I.e. if the filter was made earlier and I press that button, filter resets and filter criteria is cleared, but if that table was not filtered earlier, I am getting this error. Any way to fix that? I assume the way to fix that would be with IF condition, but I'm not sure...

 

[Worf]

Try this:

Sub Pagrindinis_iðvalyti()
' solution 1 = check for applied filter
' solution 2 = ignore the error (this code)
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    Range("L12").Activate
    Range("B12:C13,D12:E13,F12:F13,G12:H13,I12:K13,L12:M13,O12:O13").ClearContents
    Application.Goto Reference:="R1C1"
End Sub

 

[qqbbppdd]

Thank you, works wonderfully! Everything is pretty much working now thanks to you, of course... The day after tomorrow I'll be showing this workbook to my informatics teacher, hopefully I'll score an ace. I'll let you know, Obrigado pelo seu tempo que você gastou me ajudar!

 

[qqbbppdd]

I've scored an ace from this task! Yet again thank you for your time and help. I guess mods can lock this thread now.

 

[Worf]

I'm glad all went well. Threads normally stay open even after been solved; it would be interesting if the OP could click a "Solved" button to mark the thread, but I don't think this feature is available at this Forum.