msg/input box

[Trevor3007]

hi.

On the worksheet named 'timesheet' I want to put a msgbox to state "input date In Cell A2" and a input box to appear & place the applicable in a2. Then move to the worksheet called 'Sheet1' . Again I need a msg box to say"Attach File" and move the cursor H2 and upon enter run the following code/macro (macro is called ThisWorkbook.GetFilePath):-



Sub GetFilePath()
Dim dialogBox As FileDialog
Set dialogBox = Application.FileDialog(msoFileDialogOpen)




'Set the display properties - these are optional
'All the settings must be applied before the .Show command




'Do not allow multiple files to be selected
dialogBox.AllowMultiSelect = False




'Set the title of of the DialogBox
dialogBox.Title = "Select a file"




'Set the initial path to :
dialogBox.InitialFileName = "C:\data"




'Show the dialog box and output full file path and file name
If dialogBox.Show = -1 Then
ActiveCell.Value = dialogBox.SelectedItems(1)
End If
End Sub


Hoping someone can help & solve.

KR
Trevor3007

 

[JackDanIce]

Untested, however, replace all of your code with following and try:

Sub GetFilePath()

    Dim dialogBox As FileDialog
    Set dialogBox = Application.FileDialog(msoFileDialogOpen)
        
    Call GetInputs
          
    'Do not allow multiple files to be selected
    dialogBox.AllowMultiSelect = False
    
    'Set the title of of the DialogBox
    dialogBox.Title = "Select a file"
    
    
    'Set the initial path to :
    dialogBox.InitialFileName = "C:\data"
    
    'Show the dialog box and output full file path and file name
    If dialogBox.Show = -1 Then ActiveCell.Value = dialogBox.SelectedItems(1)
    
End Sub

Private Sub GetInputs()

    With sheets("timesheets")
        .Select
        .Cells(2, 1).Value = InputBox("Please enter date value: ")
    End With
    
    With sheets("Sheet1")
        .Select
        .Cells(2, 8).Select
        MsgBox "Attach file", vbOKOnly
    End With
    
End Sub

 

[Trevor3007]

Untested, however, replace all of your code with following and try:

Sub GetFilePath()

    Dim dialogBox As FileDialog
    Set dialogBox = Application.FileDialog(msoFileDialogOpen)
        
    Call GetInputs
          
    'Do not allow multiple files to be selected
    dialogBox.AllowMultiSelect = False
    
    'Set the title of of the DialogBox
    dialogBox.Title = "Select a file"
    
    
    'Set the initial path to :
    dialogBox.InitialFileName = "C:\data"
    
    'Show the dialog box and output full file path and file name
    If dialogBox.Show = -1 Then ActiveCell.Value = dialogBox.SelectedItems(1)
    
End Sub

Private Sub GetInputs()

    With sheets("timesheets")
        .Select
        .Cells(2, 1).Value = InputBox("Please enter date value: ")
    End With
    
    With sheets("Sheet1")
        .Select
        .Cells(2, 8).Select
        MsgBox "Attach file", vbOKOnly
    End With
    
End Sub

Morning JackDanIce

FANTASTIC!!!

works a treat ....thank you sooooo much. I would of not sorted myself looking at the code you complied.

Have a great day & many thanks again.

KR
Trevor3007

 

[JackDanIce]

You're welcome, glad it's resolved

 

[Trevor3007]

You're welcome, glad it's resolved

NP...

I can now cross that one off my list as I have a few others awaiting that I have asked Mr Excel to sort, but so far no one has came back to me or there good advice was not suitable.

Many thanks again
KR
Trevor3007

 

[Trevor3007]

Untested, however, replace all of your code with following and try:

Sub GetFilePath()

    Dim dialogBox As FileDialog
    Set dialogBox = Application.FileDialog(msoFileDialogOpen)
        
    Call GetInputs
          
    'Do not allow multiple files to be selected
    dialogBox.AllowMultiSelect = False
    
    'Set the title of of the DialogBox
    dialogBox.Title = "Select a file"
    
    
    'Set the initial path to :
    dialogBox.InitialFileName = "C:\data"
    
    'Show the dialog box and output full file path and file name
    If dialogBox.Show = -1 Then ActiveCell.Value = dialogBox.SelectedItems(1)
    
End Sub

Private Sub GetInputs()

    With sheets("timesheets")
        .Select
        .Cells(2, 1).Value = InputBox("Please enter date value: ")
    End With
    
    With sheets("Sheet1")
        .Select
        .Cells(2, 8).Select
        MsgBox "Attach file", vbOKOnly
    End With
    
End Sub

Hi JackDanIce,

so sorry to bother you.

I not sure if its just me...but I wanted the VBA to start upon the the applicable workbook opening, I pasted your code into ' thisworkbook' tan ran it with the VB area , which worked great & thanl you accordingly.

However, opened the workbook recently & your code does not activate?

Any ideas? ( Have checked internet etc for help prior to sending you this post BTW)

KR
Trevor3007