In the date field properties of the table, form, or properties manually input the format you would like. It is not required to choose one of the date formats in the drop down box. Example would be mm/dd/yyyy.
2. How do I put password to protect my entire Spreadsheet so data cannot be changed?
1. Click Tools
2. Scroll down to Protection, then Protect Sheet
3. Enter a password, Click OK
4. Re-enter password,
Click OK
3. What is Freeze Panes and how do I do it?
1. Row - Select the row below where you want the split to appear
2. Column - Select the column to the right of where you want the split to appear
3. Go to the Menu Bar
4. Click Windows
and then click Freeze Panes
4. How do I Format data in MS Excel?
1. Must Always highlight the data before formatting
2. Click Format
3. Then go to Cells
5. How do I resize Columns and Rows to better fit the data in MS Excel?
1. Move the mouse in between any two labels (Rows/numbers or Columns/letters)
2. The pointer will turn into a vertical (letters) or horizontal (numbers) line with arrows on both ends
3. Simply drag the column or row to the desired size
6. How do I combine different chart types into my Excel spreadsheet?
To combine chart types, follow these steps:
1. If the Chart toolbar isn't already displayed, right-click any Toolbar and select Chart.
2. On the chart, click the series you want to change.
3. On the Chart toolbar, click the arrow next to the Chart Type button and then select the new chart type for the series (in our example, a line chart).
Yes, you can close down all your Excel files at once by using the following instructions:
1. Hold down the Shift key.
2. Choose File + Close All from the menu. Holding down the Shift key changes Excel's File + Close command to a File + Close All command.
8. I have converted my MS Access database to a current conversion. I am getting conversion errors?
Design specs have changed in new releases of MS Access. Visit the on-line help option for "conversion and compatibility" that explains different portions of Access databases that will have difficulty converting. Some reprogramming may be required.
9. Is there a way to apply the same formatting to every sheet in a workbook in Excel?
Yes. To do this, you will need to right click on one of the worksheet tabs and then choose Select All Sheets. After you do this any formatting that you apply or text you enter will show up on all the sheets in your workbook. In order to eliminate certain sheets from the changes, hold down the Ctrl key and click on the tab of the worksheet you want excluded from the others. You can also group sheets by holding the shift key and selecting the worksheet tab.
10. How can I identify which cells in my spreadsheet have a formula and which do not in MS Excel?
Option A:
1. Choose Edit + Go To (or press Ctrl + F5).
2. Select Special.
3. Select Formulas.
4. Click OK.
Option B:
1. Choose Tools + Options.
2. Select the View Tab
3. In Window Options choose the check box 'Formulas'.
4. Click OK
11. How can I printout the formulas in an Excel spreadsheet - rather than the results?
The trick is to change the way Excel displays the worksheet before you choose to print. Check the box Tools, Options, View, Formulas and you'll see the formulas appear in each cell (with the columns changed to fit). When you print the sheet the formulas will be printed instead of the values
12. Is it possible to change the color and font of the sheet tabs?
Yes we can change the color of sheet tabs. By right clicking on sheet tabs and you will get option change color but i didn't find any option to change the font of sheet tabs.
13. How do I find the first empty cell in column A?
If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
End If
14. How can I unprotect a VBA project using code?
You cannot. A workaround is to simulate keystrokes with the SendKeys method
Worksheets("MySheet").Unprotect password:="drowssap"
'your code here
Worksheets("MySheet").Protect password:="drowssap"
Be sure to protect your macro code to hide the sheet password.
Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can't modify other cells or alter any part of the Excel environment. (You may be able to use a worksheet change event to call the macro.)
17. Is there a way to protect the macros I create so people can not see or alter them?
Go to Tools > VBAProject properties, lock the project for viewing, and enter a password.
18. I want to show a userform each time my file is opened?
Combine the two solutions above:
Private Sub Workbook_Open()
UserForm1.Show
End Sub
or
Sub Auto_open()
UserForm1.Show
End Sub
19. I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1?
Assign the toolbar button to this macro, which should be in a standard VBA module:
Sub ShowForm ()
Userform1.Show
End Sub
20. How do I run a macro every time a certain cell changes its value?
There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring has changed!
'Do whatever you need to do...
End If
End Sub
There are buttons from the Forms toolbar and there are buttons from the Control Toolbox. If "Assign Macro" is not an option then it's from the Control Toolbox.
22. I have a user defined function that does not recalculate?
Include all the cells that your UDF depends on in the argument list. Or enter this as the first statement in your Function:
Application.Volatile
This will cause the function to be executed whenever a calculation occurs in the workbook
23. My Stop Recording toolbar has disappeared. How do I get it back?
To reactivate the Stop Recording toolbar:
1. Choose Tools | Macro | Record New Macro
2. Click OK
3. Choose View | Toolbars | Stop Recording
4. Click the Stop Recording button (the blue square)
The next time you record a macro, the toolbar should automatically appear.
24. Can I ask my user for confirmation before executing the macro?
Sub AskAndDo()
If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo _
Then Exit Sub
'Code goes here instead of
MsgBox "Actions here"
End Sub
Application.DisplayAlerts = False
'code to save, overwrite, delete, whatever goes here
Application.DisplayAlerts = True
