VBA Automation advice

[Dermot]

I have kept my explanation as simple as possible.

I tried using the macro recorder to record the code I want but didn't
understand the steps I should follow when using the recorder to achieve the
data input Month Number / Date to get the list I want as described
below........

Problem
I want to create a list of months as shown below
Column A Column B
1 Oct 04
2 Nov 04
3 Dec 04
etc..........................

At the moment I create it manually
Column A = ROW() copied down.
Column B = Custom date format mmm-yy copied down.
10 and Oct 04 copied down would produce 1 to 10 .....Jan 04 to Oct 04

Question

I have place a command button on the work sheet and named it "NewForm"

I want to create code to attach to the command button which when run will
display a form with two input boxes in sucession 1. Number of Months and 2.
Date

The result being a list of numbered months.

Any sample code / advice would be much appreciated.

 

[Dan R.]

Dermot,

Here's one way:


Private Sub NewForm_Click()
Dim months As Variant, i As Integer
Dim mydate As Variant
Dim m As Integer, d As Integer, y As Integer

On Error GoTo ErrHandler
months = Application.InputBox("Number of months:")
mydate = Application.InputBox("Start date (mm/dd/yy):")

m = Left(mydate, 2)
d = Mid(mydate, 4, 2)
y = Right(mydate, 2)

If months <> False And mydate <> False Then
For i = 1 To months
Cells(i, 1) = i
Cells(i, 2) = i & "/" & d & "/" & y
Next
End If

Exit Sub

ErrHandler:
MsgBox "Error #" & Err.Number & vbLf & _
Err.Description, vbCritical, "Error"

End Sub

 

[Dan R.]

Made a correction, try this one.


Private Sub CommandButton1_Click()
Dim months As Variant, i As Integer
Dim mydate As Variant
Dim m As Integer, d As Integer, y As Integer

On Error GoTo ErrHandler
months = Application.InputBox("Number of months:")
mydate = Application.InputBox("Start date (mm/dd/yy):")
If months = False Or mydate = False Then Exit Sub

m = Left(mydate, 2)
d = Mid(mydate, 4, 2)
y = Right(mydate, 2)

For i = 1 To months
Cells(i, 1) = i
Cells(i, 2) = i & "/" & d & "/" & y
Next

Exit Sub

ErrHandler:
MsgBox "Error #" & Err.Number & vbLf & _
Err.Description, vbCritical, "Error"

End Sub

 

[Dermot]

Hi Dan

Thanks for the code.

 

[Dermot]

Hi Dan

Can you tell me how I can edit the properties of the Command Button I
attached the code to? Although I have it working, I can't gain access to the
properties to change the button caption....is this because it is embedded in
the worksheet?

 

[Dan R.]

Go to View...Toolbars...Control Toolbox. Click the 'Design Mode'
button. Then right click your button and hit 'Properties'.

Or through code:

NewForm.Caption = "My Button"

 

[Dermot]

Hi Dan
Thanks for the reply.
I figured out entering design mode just after posting.
Thanks for the additional code: NewForm.Caption = "My Button"

Answer one more question please....
Can you tell me what the purpose / difference is between the Form control
Toolbox and the VBE Control Toolbox

Thanks in advance.

 

[dan dungan]

Hi Dan,

I'm attempting to better understand coding, so I copied this code and
entered 12 in the number of months and 11/26/07 as the start date.

The code returned the following data:

1 1/26/2007
2 2/26/2007
3 3/26/2007
4 4/26/2007
5 5/26/2007
6 6/26/2007
7 7/26/2007
8 8/26/2007
9 9/26/2007
10 10/26/2007
11 11/26/2007
12 12/26/2007

This doesn't show my start date in #1.

To get the data to start at my start date, I changed the code from:
Cells(i, 2) = i & "/" & d & "/" & y
to:
Cells(i, 2) = m + i & "/" & d & "/" & y

Then it returns

1 12/26/2007
2 13/26/7
3 14/26/7
4 15/26/7
5 16/26/7
6 17/26/7
7 18/26/7
8 19/26/7
9 20/26/7
10 21/26/7
11 22/26/7
12 23/26/7

How would I accommodate the change in year?

Dan D.

 

[dan dungan]

Hi Dan,

I also don't understand:

Dim months As Variant, i As Integer
Dim mydate As Variant
Dim m As Integer, d As Integer, y As Integer

Why not dim months and mydate as integer?

Dan