PDA

View Full Version : [SOLVED] Auto open folder and click button help



shydaddy
06-23-2005, 12:24 PM
I have two questions, first, in the attached file, I need the VBA stuff in Sheet1 (MOD 3) under Sub CommandButton3_Click() to work when the button is clicked. I am very new at VBA and need some help with this. The other question also deals with the VBA under Sub CommandButton3_Click(). This opens up a folder but requires someone to press OK. I want the user to be able to push the Command Button 3 that is labeled "Click Here to Open File and it will open the folder, extract the files in the folder and any subfolders, open up a new worksheet and place hyperlinks to all the files from the folder.

Thanks for any and all help that you can offer.

austenr
06-23-2005, 02:01 PM
If you mean this sub:


Sub CommandButton3_Click()
End Sub

It's empty...

austenr
06-23-2005, 02:06 PM
You might also consider using this statement in the Sub that resets the form:


Application.ScreenUpdating = False

at the beginning of the sub and this:


Application.ScreenUpdating = True

at the end of the Sub. This keeps the screen from flickering and with all of the colors you have going on it is a little annoying.

Bob Phillips
06-23-2005, 03:32 PM
The other question also deals with the VBA under Sub CommandButton3_Click(). This opens up a folder but requires someone to press OK. I want the user to be able to push the Command Button 3 that is labeled "Click Here to Open File and it will open the folder, extract the files in the folder and any subfolders, open up a new worksheet and place hyperlinks to all the files from the folder.

You will find some code here http://tinyurl.com/7l4lv that creates a list of files in a directory and its sub, all hyperlinked.

shydaddy
06-23-2005, 05:49 PM
If you mean this sub:


Sub CommandButton3_Click()
End Sub

It's empty...

Sorry, The VBA that needs fixed is below Sub CommandButton3 and I want it to work when button3 is clicked.

shydaddy
06-23-2005, 08:52 PM
austner - thanks for the tip about the screen flashing. That helps a lot.

If anyone has any ideas on how to fix the issue with Sub CommandButton3_Click, please let me know. I have been searching the web and trying out different things for two weeks now and still haven't figured it out.

shydaddy
07-07-2005, 06:58 AM
Well, figured it out on my own. I placed a CONCATENATE code in cell N2 that includes the file path ie.

=CONCATENATE("G:\File Path\", E2, " - ", E3, ".xls")

Then under the command button 3, modified the code to read:


Private Sub CommandButton1_Click()
' Runs Macro to save the Target Sheet.
ActiveWorkbook.SaveAs Filename:=Range("N2"), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub