A Couple of Beginner Questions

[Powlaz]

1. Where can I learn how to write Macros?

2. I know how to create new "tabs" in Excel. Can I
create a "tab" that is a subtab? For instance, if my
workbook consists of a "tab"(spreadsheet) named "Colors"
and another spreadsheet named "Shapes", how do I create
subcategories of the spreadsheet "Colors" and name them
Black, Blue, Yellow etc.? "Shapes" and "Colors" are two
spreadsheets that need to be subdivided into smaller
spreadsheets.

3. I need to remove a group of checkboxes that are
layered one on top of the other. There are quite a few.
How can I select them all (when one is on top of the
other I can only seem to select the top one) so I can
delete them?

4. I have created a time card to track the time I start
my day and finish my day, etc. All of the data fields
that I will enter my starting and finishing times into
default to AM. Is there a way to get them to default to
PM or, better yet, is there a way to get them to read the
Windows clock and enter that time in if I just click in
the cell?

5. When I export a large amount of info from Access to
Excel, "+" (Expand) and "-" (Contract) symbols with
sliders show up on the left of the Excel spreadsheet.
How can I include them in a blank/new spreadsheet?

6. I used data validation on a spreadsheet to make sure
that if "H" was entered into A1 than an error message
would pop up if the user tried to enter anything into
B1,C1 or D1. However, if the user enters data into
B1,C1,D1 and then goes back to A1 and enters "H" the
error doesn't engage. How can I make sure that b1,c1,d1
will not take user input AND will erase user input if "H"
is entered into A1?

I've been saving these questions for a couple of weeks.
Thank you for taking the time to answer them all.

Matt

 

[Dave Peterson]

Interspersed.

1. Where can I learn how to write Macros?

Hang out in the newsgroups answering (or just reading) posts.
Buy a book --
See Debra Dalgleish's list at:
http://www.contextures.com/xlbooks.html
John Walkenbach's book gets good reviews.

take a course at your local community college

2. I know how to create new "tabs" in Excel. Can I
create a "tab" that is a subtab? For instance, if my
workbook consists of a "tab"(spreadsheet) named "Colors"
and another spreadsheet named "Shapes", how do I create
subcategories of the spreadsheet "Colors" and name them
Black, Blue, Yellow etc.? "Shapes" and "Colors" are two
spreadsheets that need to be subdivided into smaller
spreadsheets.

There aren't subtabs, but you could copy the existing sheet and edit the heck
out of it until you're happy. (do it for each subcategory). You can use a nice
name to make it look like it belongs with the other worksheet.

3. I need to remove a group of checkboxes that are
layered one on top of the other. There are quite a few.
How can I select them all (when one is on top of the
other I can only seem to select the top one) so I can
delete them?

If you show the drawing toolbar, you can use the arrow icon to lasso those
checkboxes and hit the delete key. (If you used checkboxes from the
controltoolbox toolbar, you'll have to go into design mode--also on that
controltoolbox toolbar.)

4. I have created a time card to track the time I start
my day and finish my day, etc. All of the data fields
that I will enter my starting and finishing times into
default to AM. Is there a way to get them to default to
PM or, better yet, is there a way to get them to read the
Windows clock and enter that time in if I just click in
the cell?

Hit ctrl-: (control-shift-semicolon)
format the way you like.

5. When I export a large amount of info from Access to
Excel, "+" (Expand) and "-" (Contract) symbols with
sliders show up on the left of the Excel spreadsheet.
How can I include them in a blank/new spreadsheet?

I don't use access, but it sounds like it's either Data|group or data|subtotal
to me.

6. I used data validation on a spreadsheet to make sure
that if "H" was entered into A1 than an error message
would pop up if the user tried to enter anything into
B1,C1 or D1. However, if the user enters data into
B1,C1,D1 and then goes back to A1 and enters "H" the
error doesn't engage. How can I make sure that b1,c1,d1
will not take user input AND will erase user input if "H"
is entered into A1?

You'd need an event macro (worksheet_change that cleared(?) those other cells.

In fact, I'd put all the validation into the macro.

Right click on the worksheet tab that should have this behavior and select view
code.

Paste this in and try it out:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myKeyCell As Range
Dim myRngToCheck As Range

Set myKeyCell = Me.Range("A1")
Set myRngToCheck = Me.Range("B1,C1,D1")

If Target.Cells.Count > 1 Then Exit Sub

'On Error GoTo errhandler:

Application.EnableEvents = False
If Not Intersect(myKeyCell, Target) Is Nothing Then
'you're in A1
If LCase(Target.Value) = "h" Then
If Application.CountA(myRngToCheck) > 0 Then
myRngToCheck.ClearContents
MsgBox "cleared: " & myRngToCheck.Address(0, 0) & "!"
End If
End If
Else
If Target.Value <> "" Then
If LCase(myKeyCell.Value) = "h" Then
Target.ClearContents
MsgBox "Not while: " & myKeyCell.Address(0, 0) & " has an H!"
End If
End If
End If

errhandler:
Application.EnableEvents = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

and for notes about events, you can visit David's page:
http://www.mvps.org/dmcritchie/excel/event.htm

or Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

 

[Lady Layla]

Just a quick addition to the advice Dave gave you.

In regards to learning macros, another way to help you learn it is to turn on
the macro recorder when you are doing different tasks, then look at the macro
that was recorded.


: 1. Where can I learn how to write Macros?
:
: 2. I know how to create new "tabs" in Excel. Can I
: create a "tab" that is a subtab? For instance, if my
: workbook consists of a "tab"(spreadsheet) named "Colors"
: and another spreadsheet named "Shapes", how do I create
: subcategories of the spreadsheet "Colors" and name them
: Black, Blue, Yellow etc.? "Shapes" and "Colors" are two
: spreadsheets that need to be subdivided into smaller
: spreadsheets.
:
: 3. I need to remove a group of checkboxes that are
: layered one on top of the other. There are quite a few.
: How can I select them all (when one is on top of the
: other I can only seem to select the top one) so I can
: delete them?
:
: 4. I have created a time card to track the time I start
: my day and finish my day, etc. All of the data fields
: that I will enter my starting and finishing times into
: default to AM. Is there a way to get them to default to
: PM or, better yet, is there a way to get them to read the
: Windows clock and enter that time in if I just click in
: the cell?
:
: 5. When I export a large amount of info from Access to
: Excel, "+" (Expand) and "-" (Contract) symbols with
: sliders show up on the left of the Excel spreadsheet.
: How can I include them in a blank/new spreadsheet?
:
: 6. I used data validation on a spreadsheet to make sure
: that if "H" was entered into A1 than an error message
: would pop up if the user tried to enter anything into
: B1,C1 or D1. However, if the user enters data into
: B1,C1,D1 and then goes back to A1 and enters "H" the
: error doesn't engage. How can I make sure that b1,c1,d1
: will not take user input AND will erase user input if "H"
: is entered into A1?
:
: I've been saving these questions for a couple of weeks.
: Thank you for taking the time to answer them all.
:
: Matt

 

[onedaywhen]

The VBA user's three paradigm shifts:

1. Discovering the code the macro recorder writes is not the same code
a (usually human) VBA developer would write;
2. Discovering they can reference other components e.g. ADO;
3. Discovering custom objects using Class Modules.