Access: Select All Items in a List Box

Last week I was working on a Microsoft Access database I've been building and was making some changes to a form that allows users to select one or many items from a list box in order to set criteria for a report. By default, I wanted everything in the list box to be selected upon opening the form. Unfortunately there didn't seem to be a built-in "select all by default" option, so I put a little bit of code into the Form_Open event.

It did the trick for me, so I thought I'd share it with you today in case you're looking for the same thing:

Dim i As Integer

For i = 0 To Forms!frmFormName!lstListBoxName.ListCount - 1
Forms!frmFormName!lstListBoxName.Selected(i) = True
Next i

For the sake of any VBA beginners out there, I'll just remind you that you're going to want to change "frmFormName" to the name of your form, and "lstListBoxName" to the name of your list box.

Read More

Excel: Show Values for Empty Cells in PivotTable

Do you have a PivotChart in Microsoft Excel and you would like your empty cells to actually show something like "0" or "N/A" instead of being blank?

Right-click anywhere in the PivotTable and then click 'PivotTable Options'. Make sure you're in the 'Layout & Format' tab, and then place a checkmark next to 'For empty cells show:'. Use the text box just to the right to enter what you would like to appear for any empty cell. When you're done, press the 'OK' button to close the PivotTable Options window.

Your changes should go into effect right away.

Read More

OneNote: Change the Default Font

Yes, you can change the default font in Microsoft OneNote!

As nice as Calibri is, you might find that you would like to use a different font by default for your OneNote notebooks. You can change this quite easily by clicking on the 'File' menu on the ribbon, and then choosing 'Options'. The OneNote Options dialog box comes up, and you'll see the 'Default font' section here for you to alter if you so desire.

Read More

Access: Unhide Objects or Groups in Navigation Pane

I like to use custom groups to organize objects in my Microsoft Access databases. In any case, yesterday I decided that I no longer wanted to see one of those groups in the list, so I right-clicked it and chose 'Hide'. It was great! It disappeared completely from sight, and I was happy.

...happy until I realized that I wanted to see those objects again. Usually I don't have much trouble with this sort of thing, but I'm writing this post today because I found it particularly difficult to unhide that group. Something tells me I'm not the only person who has experienced this frustration.

If you want to unhide those objects/groups, what you first are going to want to do is to configure your navigation settings to show hidden objects. Go to the 'File' tab on the ribbon, and select 'Options'. You should be on the 'Current Database' tab by default. Just one section down, you'll find 'Navigation'. Click on the 'Navigation Options...' button. Next, select the 'Show Hidden Objects' checkbox (in the bottom left-hand corner). Press 'OK' twice to confirm, and now you'll see anything that you've hidden in your navigation pane. Hidden items are dimmed, so you can easily spot them.

Now you are free to right-click and 'Unhide' anything you like. When you're done, you're probably going to want to go and uncheck that checkbox to make sure that hidden stuff stays hidden again. Your choice, though!

Hopefully this will save someone the time that I spent trying to figure this out. Until next time... have a great day!

Read More