Input Box Assistance

[DAyotte]

Hello All,

I'm just getting started with VBA - trying to teach myself. I was lent a book by a colleague and that's helped a lot, but I have an issue for this particular project that I can figure out. I'm sure it's a bit complex for a first time project, but I wanted to tackle something that would be useful.

There is a particular report we receive on a daily basis that has 2 separate worksheets. Each worksheet is fairly different, and we have to format them into a uniform template for upload into Access. I started with a user form, and it simply asks for the date of the report. I want the macro to pause until that information is received from the user, and then continue. Now, the date is needed in the formatting of both worksheets, so I would like to call on the same user form in in each macro - otherwise I would just write it into the "OK" button.

I hope this makes sense. Also, if it's not too much to ask - could any code provided be explained? It's easy to copy and paste - but I would like to understand it as well.

 

[JLGWhiz]

Hi DAyotte
If you are using a UserForm, then it would be assumed that either a label or text box is being used for the user to enter the date. If both sheets require the same date to be entered, it can be done in one shot with two lines in the underlying code to the UserForm, as and example:

Sheets(1).Range("B2") = CDate(Me.TextBox1.Text) 'The ranges are arbitrary for illustration only
Sheets(2).Range("C2") = CDate(Me.TextBox1.Text)

The UserForm is independent of any sheet, so it can be called at any time, in any code, so long as its functionallity (based on its underlying code) will support the intent of the calling code.

 

[DAyotte]

I guess I should have mentioned this, but sometimes, there is only one sheet. Total, there are two types.
For example:

Mon: Sheet A, Sheet B
Tues: Sheet B only
Wed: Sheet A only
Thurs: Sheet A, Sheet B
Fri: Sheet A only.
(This will vary)

Will that code still work? I feel like this would be a variable - something in the code of the macro operating to format the sheet to call on the stored data. Maybe it's just me being naive.

 

[Smitty]

This should get you started:

Public dtDate As Date

Private Function SheetExists(SheetName As String) As Boolean
    ' Returns TRUE if a sheet exists in the active workbook
    Dim x As Worksheet
        On Error Resume Next
            Set x = ActiveWorkbook.Sheets(SheetName)
                If Err = 0 Then SheetExists = True _
                Else SheetExists = False
End Function

Sub MyDate()
    dtDate = InputBox("Please enter a Report Date in mm/dd/yy format", "Report Date", Date)
    Sheets("SheetA").Range("A1").Value = dtDate
    If SheetExists("SheetB") Then
        Sheets("Sheet2").Range("A1").Value = dtDate
    End If
End Sub

HTH,