InputBox appears for each excel workbook

Gabriel Rotar

New Member
Joined
Mar 5, 2021
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I have a database of 500 excel files, on which operates multiple persons and sometimes are needed bulk changes on all files at ones.
For this reason, I'm trying to create an excel macro with an InputBox, but with the code that I wrote, the input box appears every time when a file is open.
Better said, if I want to change the creation date for instance on all 500 files, then I have to fill in 500 times the Date in InputBox, which cannot happen.
Please help me, below is my written code. Thank you!

VBA Code:
Sub Bulk_Update_CreationDate()

Dim dte As Date
Dim folder_path As String

Dim wb As Workbook
Dim ws As Worksheet

folder_path = "D:\PLs"
my_files = Dir(folder_path & "\*.xlsx")
Do While my_files <> ""

Set wb = Workbooks.Open(folder_path & "\" & my_files)

mbox = InputBox("Enter new Creation Date")
Sheets("Product Line Information").Select

If IsDate(mbox) Then
dte = CDate(mbox)
    Cells.Find(What:="Creation Date", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Cells.End(xlToRight).Select
    ActiveCell = dte
    Else
MsgBox "The entered date is not valid"
End If
Range("A1").Select
    Application.DisplayAlerts = False
          ActiveWorkbook.RemovePersonalInformation = False
          ActiveWorkbook.Save
          wb.Close True
          my_files = Dir()
        Loop
        MsgBox ("All Files are Updated")
End Sub
 
Last edited by a moderator:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi, welcome to forum

Try moving the inputbox outside the do loop that opens the files & see if this does what you want

Rich (BB code):
Sub Bulk_Update_CreationDate()
    Dim mbox        As Variant
    Dim dte         As Date
    Dim folder_path As String
    
    Dim wb          As Workbook
    Dim ws          As Worksheet
   
    Do
        mbox = InputBox("Enter New Creation Date", "Enter Date")
        'cancel pressed
        If StrPtr(mbox) = 0 Then Exit Sub
    Loop Until IsDate(mbox)
    
    dte = DateValue(mbox)
   
    folder_path = "D:\PLs"
    my_files = Dir(folder_path & "\*.xlsx")
    
    Do While my_files <> ""
        
        Set wb = Workbooks.Open(folder_path & "\" & my_files)
       
        'rest of code

Dave
 
Upvote 0
Solution
Hi Dave,

Thank you very much!
Doesn't solve the main issue, the InputBox still appears. Now the message "The entered date is not valid" isn't dispalyed anymore, it just remains still the InputBox empty.
On the other hand, there are improvements, so if I just could get over the InputBox display, I can declare myself satisfied.
If you still have other ideas of what can I try further, please let me know!
Thank you!
 
Upvote 0
In updating your code did you delete the line shown in bold in the Do loop?

Rich (BB code):
Sub Bulk_Update_CreationDate()



folder_path = "D:\PLs"
my_files = Dir(folder_path & "\*.xlsx")

Do While my_files <> ""

Set wb = Workbooks.Open(folder_path & "\" & my_files)

mbox = InputBox("Enter new Creation Date")
Sheets("Product Line Information").Select

rest of code
 
Upvote 0
Hello,

No I didn't... I did not understood that I have to do that... But I did now, and this is a solved case!
Thank you so, so much...!
Be blessed Dave!
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top