Excel 2007/2010 transfer passengers - Toolbars

Tuesday, 6 January 2015

Toolbars

 
Toolbars have changed completely in Excel 2007.

Here the Ribbon (Ribbon ) was created.
Whether this is good or bad, will have the time.

 

But now some projects are on the Web to bring back the old feeling and at least bring some spare for custom toolbars.

So we 're on the subject.
The usual way to create toolbars no longer exists. Conclusion from the end.

But what happens to toolbars that are created by either macros or connected with toolbars from previous versions.

This being pounded mercilessly by Excel 2007 in the area of ​​add-ins.

This also applies to toolbars or menus that are created by Add Ins.

But and ..
 

Before you complete despair :
There are ways to influence the ribbon. These are at first sight not entirely trivial but even here there will certainly be in the near future remedy.

In Online Excel I will soon write an article about it hopefully .

Here , in my opinion runs through the thread to keep inexperienced users as possible from it.
This will only lead to this ribbon will soon be so overloaded that it takes a 500 -inch monitor.

But to prove that it is, here is a small example folder.
It contains two macros for testing only. You can see.
When you open the wallet, a new point is inserted next to Start up : Online Excel.
Once you close the folder, this will disappear.

Here is an example: Example file

comments | | Read More...

Excel 2007/2010 transfer passengers - macro recording

Excel 2007 - Macro Recorder

 
Aufzeichen macros etc .. has also been well hidden.
They come to it as follows :

About the Office button on the top left and bottom right in the opened dialog Excel Options.

 

Then Frequently in the Excel Options dialog top left Now you can use the right in the upper third of a hook at:

Show Developer tab in the Ribbon set.

Now just OK and you now have in your ribbon an additional menu :
 

Developer Tools
Now enjoy browsing it.

Note that if you pressed record macro , this command changes into Stop Recording and stop so that the macro recorder. Stop command you also have the bottom left of your status bar.


Short Conclusion

 
The optics - especially of terminating a recording - I do not just succeeded.

Added to this is that the macro recorder does not work as it was supposed to , anyway, after my brief experiments with it. Well, we'll see , but it's definitely good if you still have a different version of Excel , where you can record something has to.

comments | | Read More...

Duplex printing and its case

Monday, 5 January 2015

Duplex printing and its case

General information on duplex printing

A wonderful thing is that it is now possible with almost any printer to print on the front and back of a sheet of paper. The so-called duplex printing.

A prerequisite for this is also enabled in the printer driver that option. In Excel , you can set it like this:

 

- "File" menu
- " Page Setup ... "
- In the " Paper Size" on the "Print" button
Select the printer you want -
- On the "Properties ..." button

This will open the configuration dialog for the selected printer . However, since this depends on the printer , I can suggest no universal approach here. You have to look where hiding this option itself. You can call themselves " duplex printing ", " both sides ", " Two -Sided" or similar. The Setup dialog box of the printer they leave with "OK" .

If the two-sided printing enabled in the printer ( driver ), a spreadsheet that does not fit on a printed page, automatically printed on the back on.

This also goes wonderfully with several table and chart sheets.
If you want to , for example, Print a list in Sheet1 and an associated graph in the leaf diagram 1 on the front and back of a paper page , you mark with the Ctrl - key registers Table 1 and diagram 1 and printed on a duplex printer as usual. This will result in most cases, the desired two-sided printing.
 

The trap
Double-sided printing still does not work ?

This happened to me more than once that I just failed to print on both sides .
Exasperated, I avoided the problem with a complex VBA routine that everything copied to a spreadsheet created and page breaks.

By chance I recently discovered the cause of failure : If the duplex printing is not working as designed , you must apply to all affected table and chart sheets individually (without grouping of leaves !) Check the printer settings and optionally enable duplex printing . Because it can be , for whatever reason , that the Duplex setting is not active in this / was.

It can happen that individual leaves do not accept this setting , despite duplex activation for the grouping of leaves.

Mnemonic:

Does the duplex printing is not as desired, set for each page duplex printing (without printing) and try it again then.

comments | | Read More...

Blattschutz- cell protection - protect formulas

Blade guard - cell protection - protection formula

Preface :

Your everlasting diligence you have equipped built with complex formulas varied tables and now ... is your fear of course great that this was all for naught, 

because some bumbling fingers - and this may well be your own - destroy formulas, move rows, columns, Clear and and and ...However, you can of course make provisions ( Not to mention that you should of course have backups).

But before we first begin a : Protecting what we are discussing here is not immune to bad boys .

To test this, take a new empty wallet and there the first worksheet

preparation:

Write in cell B1 a 26 in a 16 C1 and D1 = B1 + C1

If you now have the cell marked D1, shown in the formula bar the extraordinary again not reproducible ingenious formula , which I bought for you under the moonlight by a werewolf .

Select the range B1 to D1 , just as you would want to reformat it . And we also need the dialog to format . This call easiest using the keyboard shortcut CTRL + 1 auf.Es The dialog Format Cells . And in this dialogue , there are top-right corner the Protection tab . In this tab, there are 2 check boxes:

Closed
Hidden

In general, the box of Locked is checked, this of Hidden not .
The box of Locked is responsible for whether after arming the blade guard the cell is locked.
The box of the Hidden is responsible if after arming the blade guard a possible formula of the cell is hidden.

Now put another check mark in the Hidden and exit the dialog with OK.

Cursed be the day on which the werewolf not eaten the authors of this article . Because if you click on D1, you still see the formula in the formula bar and you can change all the cells still . But give the werewolf still some time before it spoils the stomach.

Arming

All this fuss which we have previously held , was only preliminary . We need Excel now even instruct that the blade guard is enabled.

Through Excel 2003, click the Tools menu - > Protect Sheet -> Protection
From Excel 2007 in the register and check there to protect sheet

If you click there , a dialog box in which you can play around manifold opens.
Since Excel XP ( Excel 2002) because you can also set all great stuff .

But we still want to keep something serious.
The first thing that the password is eye-catching . Yep , I 'm about to fire up my time password generator and ...

Oh, for the first test, you do not need a password. Simply click on OK. This turns the blade guard without a password , ie, You can remove sheet protection without password.

Once this has happened , try times in cells B1 to D1 enter anything.
They are prevented from Excel it. And if you click the cell D1, you do not even see the formula.

All well and good - but not useful , because I want already in B1 or C1 something can enter .
To do this we raise the blade guard again: I do not trust you that you will find blade guard picked up! Well the way, if you have not entered a password, goes much faster.

After lifting the blade guard select cells B1 and C1 and call the Format dialog . Then again, Protection tab and remove the check mark from when Locked and OK.
Now turn off the blade guard again and look forward to the things and send especially the werewolf home.

Just like you wanted it , the cell is D1 banned (and even the formula hidden) and the cells B1 and C1 may be edited.

Note:
During the blade guard is off, the cells for the blade guard müsssen be formatted if they are protected with active guard and whether formulas are displayed.
Only if the guard is enabled, these settings come to fruition .


Company is more blessed than Type

For I suppose you just look at the hope that all of this is done with a few clicks .
You have to fight your way through your worksheets and decide everywhere which cells you want to edit, and where you want to hide and what ...

You also need to protect each worksheet individually , in which the blade guard is to be effectively active.

If you work with macros , then the whole is even more uncomfortable because you have to remove and restore protection in the background constantly .


A password and its consequences, and it is worth nothing
When it comes to passwords, understands the common password mouse no fun. Because even children under 3 years already know that a password like "secret " is now not really sure and you want to apply those passwords never for any password-protected access.

And now the reader will of course come up with the idea to provide his guard with a password, which can not guess even a high-performance computer in 42 billion years.

In fact, it is usually the interested reader, the sometime can not guess it and begins to cry bitterly.

You can set a password so complicated it also can think of - place on a blade guard, it is worth nothing - unfortunately.

The protective mechanism in Excel for sheet protection on a level that can pry it in 2 hours on the internet a 2 -Jähriger VBA Informed petty criminal.

So do not make the effort to think too complicated. If you are using any password, one that you will remember for sure.

And do not you remember that you can use it to protect your intellectual property.

Conclusion

Blade guard is a more than 2- Schneidig thing for me . There are very few truly meaningful situations in which I use it. Because the blade guard also hindered immensely.
Fortunately, you can E2002 since at least allow some stuff in the leaf blade guard but despite that really does not help this.

Blade guard is good if you can be absolutely sure that the user should really active in the Journal entries only . And nothing else. Then he really is - almost - really good.
Blade guard is also good when I turn it on for myself. Because then I can turn it off if necessary also for myself.

He is unfit to formulas to hide safely in any case.

And he's definitely unfit for it to make a journal entry really safe . This prevents Microsoft already !

And he is unfit in any case , to the fall ... oops , I think that's another topic.

comments | | Read More...

Move within tables

Sunday, 4 January 2015

The keyboard is the alpha and omega in moving tables.
It is faster than the mouse often surprised what they can .

Keyboard shortcuts are the ultimate !

Take a table in Excel with lots of data and sample a little bit of:

Ctrl + Right Arrow key
Ctrl + Left arrow key
CTRL + Down arrow key
CTRL + Arrow Key
CTRL + End key
CTRL + Pos1 key

Yes, it is fun right?

You want quickly into the cell Z2000 ?
 

No Problem:
The bore is on the Edit menu and select Go to there ,
the Tastenkombinierer takes CTRL + G
both then type in the dialog Z2000 and are happy .

The alternative writes in Z2000 's name field and press the Return key .

But as you can see, there are a few ways to move quickly in tables and I have a couple of them demonstrated here , try yourself further .

comments | | Read More...