Posts by max_lux

Re: Save As (Excel Workbook/File as a Cell Text)

yes i have a button on sheet2 just for my purposes - ie when i edit that sheet and use worksheet tabs, sometimes when i select that sheet the tabs dont show, so i have a go_to button on the sheet i remove prior to copying to new book

Re: Save As (Excel Workbook/File as a Cell Text)

Here's an example - with the excess code stripped

Re: Save As (Excel Workbook/File as a Cell Text)

Private Sub Export_sheet2_Click()
    ActiveWorkbook.Unprotect Password:="___" 'unprotect workbook to make sheet2 visible
    Worksheets("sheet2").Visible = xlSheetVisible
    Sheets("sheet2").Select
    Sheets("sheet2").Copy 'copy sheet2 to new book
    ActiveSheet.Unprotect Password:="___"
    ActiveSheet.Cells.Select 'remove formulas from sheet2 and leave only values
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Shapes("Button 1").Select 'remove button from sheet2
    Selection.Delete
    Worksheets(1).Calculate 'calculate then remove function
    Worksheets(1).EnableCalculation = False
    ActiveWorkbook.UpdateLinks = xlUpdateLinksNever 'set links to never
    ActiveSheet.Protect Password:="___", DrawingObjects:=True, Contents:=True, Scenarios:=True ' lock sheet2
    ActiveSheet.EnableSelection = xlUnlockedCells
    
    Dim strName As String 'set variable


On Error GoTo InvalidName 'save file as cell name
    strName = sheet1.Range("A1")
    ActiveWorkbook.GetSaveAsFilename (strName)
Exit Sub
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name.", vbCritical, "Filename Error"


    
    Application.DisplayAlerts = False
    ActiveWindow.Close 'close copied and saved sheet2
    Worksheets("sheet2").Visible = xlSheetVeryHidden 'hide sheet2
    ActiveWorkbook.Protect Password:="___" ' protect the workbook
    Sheets("sheet1").Select
End Sub

I don't use Application.Screenupdating= False so the user sees the sheet they are copying and saving

Re: Save As (Excel Workbook/File as a Cell Text)

hehe the book is almost 3 megs, can't post it - it might be to do with the fact that I am trying to save a page during the export process.

I have a very hidden sheet made visible, copied to new book, new book all cells formulas pasted over special as values (to remove links and reduce size), saved as... and then closed, sheet printed, and then made very hidden again.

I have to go to work at the moment, will be back on a bit later, thanks for your help - i will try simulate the process in a 2 page book later and post it

Re: Save As (Excel Workbook/File as a Cell Text)

cell is formatted as text - i just take the concectonate and paste the value prior to the save process, yes a02 is the sheet name

Re: Save As (Excel Workbook/File as a Cell Text)

Thanks very much, though it doesn't work for me so far, why I'm not sure, I will play with it. I appreciate your help

Comes up with the error message every time no matter what text I put in.

Dim strName As String


On Error GoTo InvalidName
    strName = a02.Range("M2")
    ActiveWorkbook.GetSaveAsFilename (strName)
Exit Sub
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name.", vbCritical, "Filename Error"

Re: Save As (Excel Workbook/File as a Cell Text)

Whatever is in the cell will be the name of the workbook....

ie: I concectonate using forumlas such as

=A1&"_"&A3&"("&A4&").xls"

...which would give me a result like...

Tacoma_Downtown(1).xls

I would like the result of this concectonate to be the workbook name, but I would like the user to be able to select where to save this workbook to, hence the "Save As..." requirement.
ie I have three folders, East, Central, West, and I want the user to be able to save the workbook to one fo these three folders.

Using the following code (obtained from the macro section this site), it is possible to save a workbook as a cell name.

Sub SaveAsCell()
Dim strName As String


On Error GoTo InvalidName
    strName = Sheet1.Range("C1")
    ActiveWorkbook.SaveAs strName
    
Exit Sub
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name.", vbCritical, "Ozgrid.com"
End Sub

How would I incorporate the...

dlganswer = Application.Dialogs(xlDialogSaveAs).Show

...so I can have the filename saved appropriately, but let the user select the target directory\ folder?

Thanks for your help

Re: Addins & DLL references

Thanks for the answer.

...so what I have done is make an installer with the DLLs and OCXs required, and have them installed and registered on the other persons machine, not overwriting if a file is already there. So far so good, though I know people are running anything from 97 to 03 versions of office, this seems to be working.

I thought that if I ran references with an addin, and then loaded the addin on another machine, that the DLLs would be loaded on it, but this isn't so?

If this is true, using something so simple as the date picker is pretty well useless as I have people that have a placemarker for it but dont see it.

I can have people manually register DLLs, but what a pain...

Thanks,

Re: Disable Text Length Error Message

This may be a redundant question. I'm going to see if the following codes work

Option Explicit 
 
Private Sub Workbook_Open() 
    Application.ErrorCheckingOptions.BackgroundChecking = False 
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Application.ErrorCheckingOptions.BackgroundChecking = True 
End Sub

Anyone know how to disable the text length exceeds 256 char error message?

Thanks,

Re: Print Then Return To Sheet

lol - nice job Norie - hehe replied before I noticed u'd changed the code Error gone!

Many thanks!

Re: Print Then Return To Sheet

if u cancel either printer or no of copies you get the error

Re: Print Then Return To Sheet

Ok that works brilliantly, but on error such as cancelling? not having the runtime error end/ debug/ help box pop up?

Re: Print Then Return To Sheet

Hmm, I'm confused. I'm attaching a simple workbook. Can you make it print sheet2 and end up back at sheet1, after printing, without selecting sheet1?

with screen updating false it still left it on sheet2

Thanks again,

Re: Print Then Return To Sheet

hehe thanks for the faste rthan lightning replies guys

I need the print dialog because each person is in a truck and has their own printer, so printer selection, copies etc is easiest done with it.

All I want to do is return to the sheet I was at before I printed the hidden sheet.

Here's some basic code.

Sub Print_M1()
    Sheets("M1").Select
    dlganswer = Application.Dialogs(xlDialogPrint).Show
End Sub

Do I just add the option explicit (as per below) to make this go back to the sheet I was at before I selected to print M1?

Option Explicit


Sub Print_M1()
    Sheets("M1").Select
    dlganswer = Application.Dialogs(xlDialogPrint).Show
End Sub

Re: Print Then Return To Sheet

Norie,

won't it still be on the DH sheet once screen updating becomes true?

Re: Print Then Return To Sheet

I guess I wasn't clear in what I wish to do.

From any (original) sheet
Select "print (another)" sheet on custom tool bar
Print (another) sheet
Return to (original) sheet whatever sheet it was on

Re: Print Then Return To Sheet

Yes I need to show the dialog box, because multiple copies may need to be printed.

Your method will not return me to the original sheet.

Thanks for the try.