Message box in excel

[RANDY IN NC]

Sorry on the last thread... site did not respond when I submited.

I am attempting to do the following... but I cannot figure out a couple of the steps..

1. I am making a copy of a worksheet from a workbook, starting a new workbook.. and doing a paste special values into the new workbook.. I either need the new workbook to come up with just one sheet or I need to delete all blank sheets... when I have attempted this I get the dialog box that says the deletion will be pementant. which I want it to answer ok and go on... but how..

2. I need to do a save as to a new file name and want it to automatically select the contents of a specific cell or combination of cells for the file name to assign.. again how

3. I am saving it as a csv file.. but again I have dialog boxes I need it to get arround...

Thank you for your help!

Randy

[Leith Ross]

Hello Randy,

1. you can turn off the dialogs and messages. Excel will use the default button as the response automatically. This will work in the case of deleting the Worksheets.

Disable Dialogs

Application.DisplayAlerts = False

Enable Dialogs
Re-enable it before you code finishes, so you will can see system messages.

Application.DisplayAlerts = True

2. Since you are working with 2 workbooks, it is a good practice to fully qualify code procedures. ThisWorkbook always refers to the workbook the code is written in. Change the Sheet name and Range to what you will be using.

ThisWorkbook.Worksheets("Sheet1").Range("A1")

3. See question 1

Sincerely,
Leith Ross

[RANDY IN NC]

Thank you I have been able to pass the dialog boxes... but I seem to be having trouble with the name... I inserted the process for the name that you indicated but I am not doing something right..

Here is what the line from my macro looked like... before. but cannot change the name as you indicate.. Please let me know what I have wrong..

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Administrator\My Documents\contract work\jackson\AR1\order2.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

I tried but get an error.."compile error expected end of statment"


Thank you..

[Tom Ogilvy]

Dim sStr as String
sStr = ""C:\Documents and Settings\"
sStr = sStr & "Administrator\My Documents\contract "
sStr = sStr & "work\jackson\AR1\"
sStr = sStr & _
ActiveWorkbook.Worksheets(1) _
.Range("B1").Text
sStr = sStr & ".csv"
ActiveWorkbook.SaveAs _
Filename:=sStr, FileFormat:=xlCSV

Might solve your problem.
Adjust to fit your requirements.
--
Regards,
Tom Ogilvy



"RANDY IN NC" <[email protected]>
wrote in message
news:[email protected]...
>
> Thank you I have been able to pass the dialog boxes... but I seem to be
> having trouble with the name... I inserted the process for the name
> that you indicated but I am not doing something right..
>
> Here is what the line from my macro looked like... before. but cannot
> change the name as you indicate.. Please let me know what I have
> wrong..
>
> ActiveWorkbook.SaveAs Filename:= _
> "C:\Documents and Settings\Administrator\My Documents\contract
> work\jackson\AR1\order2.csv" _
> , FileFormat:=xlCSV, CreateBackup:=False
>
> I tried but get an error.."compile error expected end of statment"
>
>
> Thank you..
>
>
> --
> RANDY IN NC
> ------------------------------------------------------------------------
> RANDY IN NC's Profile:
http://www.excelforum.com/member.php...o&userid=29778
> View this thread: http://www.excelforum.com/showthread...hreadid=496249
>