SOLVED

Excel - Enhance excel workbook

Occasional Contributor

Hi All, I created a workbook with one command button that give a functionality to end user to browse a file and once file chosen its name get highlighted in workbook(yellow color). In my ongoing project I want to enhance it in following manner ..

 

  1. During clicking on “Choose file” button it should allow to choose only “.txt” file else message should pop up “Invalid file format”.
  2. We should also maintain a log of History like how many files were chosen with their date. If user again going to choose another file then previous file should be moved to below cell with green color and new one should highlighted with yellow and this manner should continue.

TheTallman_0-1625506366299.png

3. Also, if user attempted to pick a same file that was already in log with green color then warning should pop up “File was picked on date ??/??/????.

 

I attached a sample file so please help how I can add these functionality to it.

 

2 Replies
best response confirmed by TheTallman (Occasional Contributor)
Solution

@TheTallman 

 

1) Change the line

UserSelectedFile = Application.GetOpenFilename

to

UserSelectedFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt")

2) Use

        Range("B8:C8").Insert Shift:=xlShiftDown
        Range("B8:C8").Interior.Color = vbYellow
        Range("B8").Value = Mid(UserSelectedFile, InStrRev(UserSelectedFile, Application.PathSeparator) + 1)
        Range("C8").Value = Date ' or Now
        Range("B9:C9").Interior.Color = vbGreen

See the attached version.

Thank you so much @Hans Vogelaar . It worked perfectly.