or Connect
Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 30

post #436 of 532
Quote:
Originally Posted by RedScarf7 View Post
So I've got a model template that I've built. I want to add a print feature to the table of contents page that would allow my boss (or whoever) to select a particular tab from a drop down menu and hit a button that would print off the pre-determined print area from the tab they desire info from. I'm pretty decent in excel, not very experienced with VBA. Recording a macro to accomplish this has been proven very slow and inconsistent.

Anyone have a good solution/recommendation for this?

I was bored at work and put together a solution to your problem. It involves a couple of command buttons, a listbox and of course VBA. I'm not sure if you're comfortable working with these without any VBA background, but to be honest it's a very good way to get started with VBA. The workbook is attached if you want to check it out.

The design and code are pretty simplistic and I'll give a little overview below.

Workbook Design

There are 4 worksheets: 1 is the 'Dashboard' which contains the listbox and buttons. The other 3 are the worksheets that are to be printed. I set the print areas on these worksheets.

Code

There are 5 chunks of code that I'll explain below.

1. Clear the Listbox

This code just removes any values that remain in the listbox. It's necessary to get rid of the listbox contents before populating the listbox in the case where the listbox contents change.

Code:
Private Sub Clear_Listbox()

ListBox1.Clear

End Sub

2. Fill the Listbox

First the code clears the listbox by referring to the 1st chunk of code above (Clear_Listbox) then it populates the listbox with the appropriate worksheets. It doesn't include hidden worksheets, and in this case I excluded the 'Dashboard' worksheet.

Code:
Private Sub Fill_Listbox()

Dim Ws As Worksheet

Clear_Listbox

For Each Ws In Worksheets
    If Ws.Name = "Dashboard" Or Ws.Visible = False Then
    Else
        ListBox1.AddItem Ws.Name
    End If
Next

End Sub

3. Refresh the Listbox

This code refreshes the listbox and is set-up behind the 'Refresh Listbox' command button.

Code:
Private Sub CommandButton1_Click()

'Refresh List
Fill_Listbox

End Sub

4. Print Selected Worksheets

This code prints all of the selected items in the listbox. The code is set-up behind the 'Print Selected Sheets' command button. I set up message boxes to inform the user that either their worksheets were printed or that they clicked the button without selecting any worksheets.

Code:
Private Sub CommandButton2_Click()

Dim listItem As Long, count As Long

For listItem = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(listItem) = True Then
        'Worksheets(listItem).PrintOut
        count = count + 1
    End If
Next listItem

If count > 1 Then
    MsgBox "The selected worksheets printed.", vbInformation, "Worksheets Printed"
ElseIf count = 1 Then
    MsgBox "The selected worksheet printed.", vbInformation, "Worksheets Printed"
Else
    MsgBox "Please select at least one worksheet to print.", vbCritical, "Select Worksheet"
End If

End Sub

5. Fill the listbox when a worksheet is clicked

This code isn't really necessary, since there's a 'Refresh List' button that does the same thing. The reason I included this is so that clicking on the 'Refresh List' button isn't necessary, just in case someone who didn't know how to use the worksheet decided to open it.

Code:
Private Sub Worksheet_Activate()

Fill_Listbox

End Sub
post #437 of 532
Quote:
Originally Posted by iceypain View Post
sorry if it's already been asked in this thread, but what's the best way to learn excel from scratch?

Quote:
Originally Posted by Biggskip View Post
One way is just experience. The more things you work on, and the more varied thing you work on the better.

Another way is to have somebody send you the most complicated and complex spreadsheet they have available and to go through it cell by cell and understand how the whole thing works.

Quote:
Originally Posted by dsgNYC View Post
Agreed. I got good because I used it at work for 8 hours per day. I also learned VBA from a book and was able to use it at work.

This should cover it.
post #438 of 532
Quote:
Originally Posted by iceypain View Post
sorry if it's already been asked in this thread, but what's the best way to learn excel from scratch?

In addition to what Biggskip said above, once you get semi-proficient you can just go back through this thread and read it; you'll learn a lot that way, too.
post #439 of 532
Quote:
Originally Posted by iceypain View Post
sorry if it's already been asked in this thread, but what's the best way to learn excel from scratch?
look at the top 10 excel functions, and apply them in work to consolidate your knowledge: vlookup, pivot table, logic functions such as IF and IS***** IF (A1 <> "apple"), advanced: data validation, index, defined ranges, offset, vba use only keyboard shortcuts, avoid using mouse.
post #440 of 532
Quote:
Originally Posted by dsgNYC View Post
Agreed. I got good because I used it at work for 8 hours per day. I also learned VBA from a book and was able to use it at work.
What book? I know excel, and know how to program (lot's of C++ and other language experience). Just need to learn how to VBA inside excel. Any other recommendations other then the Walkenbach books?
post #441 of 532
Thread Starter 
Quote:
Originally Posted by imschatz View Post
What book?

I know excel, and know how to program (lot's of C++ and other language experience). Just need to learn how to VBA inside excel. Any other recommendations other then the Walkenbach books?

VBA is super easy. You can figure it out from the help section within the VBA editor (which is surprisingly comprehensive) plus some internet. Most regular programming constructs are available - you just need to figure out what all the classes and methods are called.

What's wrong with the Walkenbach books?
post #442 of 532
Quote:
Originally Posted by imschatz View Post
What book? I know excel, and know how to program (lot's of C++ and other language experience). Just need to learn how to VBA inside excel. Any other recommendations other then the Walkenbach books?
I would recommend two things. First, have somebody send you a spreadsheet that has at least one heavy duty macro inside. Then look at the Macro and how it relates to that spreadsheet. CS is right in that it is super easy how to figure it out. Second, record a macro and then look at the code to see how it works. What I mean by recording a macro is that excel has a feature for those who want to create a macro w/o actually knowing any code. Under the Tools menu, select Macros, and then select Record Macro. You will be asked to supply a name for the macro, I usually go with "Test". Then just "do" a few things: select a group of cells, cut/copy a cell, paste a cell somewhere else, turn on a filter, turn off a filter. Then look at the macro. It will have coded VB for everything you just "did".
post #443 of 532
Ok, so I don't know if this makes sense/is possible but: I have about a dozen sheets with different test results for various subjects, formatted like A - name B - Year of Birth C - test result at time a (the top row is a series of dates) D - test result at time b and so on. Each sheet has a different test type, and each subject on the same row. Is there a way to make a new sheet for each subject, with the data C2:H2 from sheet one in row 1, C2:H2 from sheet 2 in row 2, etc? I guess I should have been doing it like this from day one right?
post #444 of 532
How many subjects do you have?
post #445 of 532
Quote:
Originally Posted by Kajak View Post
Ok, so I don't know if this makes sense/is possible but:

I have about a dozen sheets with different test results for various subjects, formatted like
A - name
B - Year of Birth
C - test result at time a (the top row is a series of dates)
D - test result at time b
and so on. Each sheet has a different test type, and each subject on the same row. Is there a way to make a new sheet for each subject, with the data C2:H2 from sheet one in row 1, C2:H2 from sheet 2 in row 2, etc?

I guess I should have been doing it like this from day one right?

You could use the INDIRECT function to present the data. Or you could reorganize the data first and then run reports by name or by subject via pivot tables
post #446 of 532
Quote:
Originally Posted by CunningSmeagol View Post
VBA is super easy. You can figure it out from the help section within the VBA editor (which is surprisingly comprehensive) plus some internet. Most regular programming constructs are available - you just need to figure out what all the classes and methods are called.

What's wrong with the Walkenbach books?
Nothing wrong with the books, just already seen that one recommended in the thread.
post #447 of 532
Quote:
Originally Posted by imschatz View Post
What book?

I know excel, and know how to program (lot's of C++ and other language experience). Just need to learn how to VBA inside excel. Any other recommendations other then the Walkenbach books?

Here's the book I used:

http://www.amazon.com/VBA-Modelers-D...832850&sr=1-33
post #448 of 532
I have a very long worksheet that I'm trying to sort in a specific way. Basically, each row is a company's name plus some other data. Each data point is repeated individually, with the company's name, in individual rows. I want to sort by frequency...so Company X, which is shown 15 times, should be on top of Company Y, which is shown only in 12 rows. But it seems the only way I Can do that is by doing alphabetical first. Any recommendations? EDIT: I think COUNTIF is the way to do it no?
post #449 of 532
Quote:
Originally Posted by Connemara View Post
I have a very long worksheet that I'm trying to sort in a specific way. Basically, each row is a company's name plus some other data. Each data point is repeated individually, with the company's name, in individual rows. I want to sort by frequency...so Company X, which is shown 15 times, should be on top of Company Y, which is shown only in 12 rows. But it seems the only way I Can do that is by doing alphabetical first. Any recommendations?

EDIT: I think COUNTIF is the way to do it no?

Countif for every row, sort by countif row.
post #450 of 532
Quote:
Originally Posted by Connemara View Post
I have a very long worksheet that I'm trying to sort in a specific way. Basically, each row is a company's name plus some other data. Each data point is repeated individually, with the company's name, in individual rows. I want to sort by frequency...so Company X, which is shown 15 times, should be on top of Company Y, which is shown only in 12 rows. But it seems the only way I Can do that is by doing alphabetical first. Any recommendations?

EDIT: I think COUNTIF is the way to do it no?

A pivot table is the easiest way - but this will require manual updates. =COUNTIF will update automatically.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread