Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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 -- Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
Hi Dan
Thanks for the code. "Dan R." wrote: 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 -- Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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." wrote: 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 -- Dan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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" -- Dan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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 R." wrote: Go to View...Toolbars...Control Toolbox. Click the 'Design Mode' button. Then right click your button and hit 'Properties'. Or through code: -- Dan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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. On Nov 14, 2:27 pm, "Dan R." wrote: 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 -- Dan |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Automation advice
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
any advice pls.. | Excel Discussion (Misc queries) | |||
Supressing the ctrl-c and other keys during word automation in automation | Excel Programming | |||
need advice | Excel Programming | |||
Almost got it !! but need advice | Excel Worksheet Functions | |||
RTD Advice | Excel Programming |