Creating Date Tabs using dates entered via form

[MartinF123]

Please note I am a very new to VBA and do not fully understand its workings.

I am using Excel 2010 and wish to be able to do the following:

Upon opening of a Workbook a form appears allowing the user to enter two dates. Upon pressing the ‘Submit’ button the two dates are used to produce a number of worksheets, each worksheet labelled with the day and date (day /month) covering the range as entered by the user.

I created a VBA form as shown



The first entry field is called textFirst and the second entry field is called textLast

The command button is labelled as Submit and has a property name of cmdOK

The VBA code for the form is as follows:

Private Sub cmdOK_Click()


If Me.textFirst.Value = "" Then
MsgBox "Please enter the first date of the Spreadsheet", vbExclamation, "Athena Dates"
Me.textFirst.SetFocus
Exit Sub
End If
If Me.textLast.Value = "" Then
MsgBox "Please enter the last date of the Spreadsheet", vbExclamation, "Athena Dates"
Me.textLast.SetFocus
Exit Sub
End If

Cells(1, 1).Value = Me.textFirst
Cells(1, 2).Value = Me.textLast

athenaEnter.Hide

End Sub

This codes works fine and puts the entries into Cell A1 for ‘textFirst’ and Cell B1 for ‘textLast’


The next stage is to use these two dates as date in the following VBA code. The code is designed to generate a tab for each day starting with the first date as entered into ‘textFirst’ right up until the date as entered in ‘textLast’


The code is as follows:

Private Sub CommandButton2_Click()

Sheets("sheet1").Select
FD = Cells(1, 1)
LD = Cells(1, 2)
LD = Day(Cells(1, 2))

n = 0
Do Until n >= LD
x = (FD + n)
If x = 1 Or x = 7 Then
GoTo 20
Else
Sheets.Add
ActiveSheet.Name = Format(FD + n, "dddd") & " " & Day(FD + n) & " " & MonthName((Month(FD + n)), False)
Sheets("Sheet1").Select
End If
20
n = n + 1
Loop


End Sub

To start this code I have generated an Active X button. However when pressed I receive an Runtime Error 13 at the point x=(FD+n)

I then created a separate Spreadsheet to just test the ‘sheet generating VBA and by manually entering the dates into Cells A1 and B1. It worked fine.

So my problem is when the form is used to collect the data and enter it into Cells A1 and B1 why does the VBA code then not like the entry. I guess it is something simple but I just can’t seem to work it out.

[pjwhitfield]

Are the cells formatted as dates or are they simply showing a text representation of the date?

Ps you need to get your code formatted inside [code] tags in your post.