Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a workshhet?
User opens a workbook containing a single sheet.
Code has disabled File Save and Save As. I need to allow the user to Save if they wish! Is the best way a button... if so how, please? I need to ensure the saved name is different from the filename opened, and that they can choose their save directory. If they Cancel then the workbook is not to be saved. Can you help, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a workshhet?
Why disable the save and saveas buttons. (did you disable Ctrl+S?) Just
intercept the save in the Workbook_BeforeSave event and handle the naming there. (cancel the save and manage it yourself) -- Regards, Tom Ogilvy "Stuart" wrote in message ... User opens a workbook containing a single sheet. Code has disabled File Save and Save As. I need to allow the user to Save if they wish! Is the best way a button... if so how, please? I need to ensure the saved name is different from the filename opened, and that they can choose their save directory. If they Cancel then the workbook is not to be saved. Can you help, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a workshhet?
I had been trying to do just that, but couldn't get it
to work. My code was: Dim wkbkname As String Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim orderno As String, jobno As String If ActiveWorkbook.Name = wkbkname Then 'user has not changed the filename 'get some example data With Range("I9") jobno = .Value If jobno = "" Then jobno = "123" End If End With With Range("J9") orderno = CLng(Mid(.Value, 2, 4)) 'what if "J9" value = "" ....error I think CHECK If orderno = "" Then orderno = "1001" End If End With MsgBox "You MUST save the file with a NEW name" _ & vbNewLine & vbNewLine & _ "Perhaps something like ..." & vbNewLine & _ " " & _ "E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls" Exit Sub End If End Sub I can remind the user, but neither force the Save nor permit the Cancel. I know I should 'Know' this. I have only module code in the ThisWorkbook module, no userforms, buttons etc. Regards and thanks. "Tom Ogilvy" wrote in message ... Why disable the save and saveas buttons. (did you disable Ctrl+S?) Just intercept the save in the Workbook_BeforeSave event and handle the naming there. (cancel the save and manage it yourself) -- Regards, Tom Ogilvy "Stuart" wrote in message ... User opens a workbook containing a single sheet. Code has disabled File Save and Save As. I need to allow the user to Save if they wish! Is the best way a button... if so how, please? I need to ensure the saved name is different from the filename opened, and that they can choose their save directory. If they Cancel then the workbook is not to be saved. Can you help, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a workshhet?
Here is a rough approach. (code is untested)
Dim wkbkname As String Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim orderno As String, jobno As String, fName as String If ThisWorkbook.Name = "ABCD.xls" Then 'user has not changed the filename 'get some example data With Range("I9") jobno = .Value If jobno = "" Then jobno = "123" End If End With With Range("J9") orderno = CLng(Mid(.Value, 2, 4)) 'what if "J9" value = "" ....error I think CHECK If orderno = "" Then orderno = "1001" End If End With Application.EnableEvents = False Cancel = True ' put up a message if you want sStr = "E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls" fName = Application.GetSaveASFileName(sStr,"Excel Files (*.xls),*.xls)") if fName < ThisWorkbook.Name and fName < "False" then thisworkbook.SaveAs fName end if Application.EnableEvents = True End If End Sub -- Regards, Tom Ogilvy "Stuart" wrote in message ... I had been trying to do just that, but couldn't get it to work. My code was: Dim wkbkname As String Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim orderno As String, jobno As String If ActiveWorkbook.Name = wkbkname Then 'user has not changed the filename 'get some example data With Range("I9") jobno = .Value If jobno = "" Then jobno = "123" End If End With With Range("J9") orderno = CLng(Mid(.Value, 2, 4)) 'what if "J9" value = "" ....error I think CHECK If orderno = "" Then orderno = "1001" End If End With MsgBox "You MUST save the file with a NEW name" _ & vbNewLine & vbNewLine & _ "Perhaps something like ..." & vbNewLine & _ " " & _ "E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls" Exit Sub End If End Sub I can remind the user, but neither force the Save nor permit the Cancel. I know I should 'Know' this. I have only module code in the ThisWorkbook module, no userforms, buttons etc. Regards and thanks. "Tom Ogilvy" wrote in message ... Why disable the save and saveas buttons. (did you disable Ctrl+S?) Just intercept the save in the Workbook_BeforeSave event and handle the naming there. (cancel the save and manage it yourself) -- Regards, Tom Ogilvy "Stuart" wrote in message ... User opens a workbook containing a single sheet. Code has disabled File Save and Save As. I need to allow the user to Save if they wish! Is the best way a button... if so how, please? I need to ensure the saved name is different from the filename opened, and that they can choose their save directory. If they Cancel then the workbook is not to be saved. Can you help, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button on a workshhet?
Many thanks for the help
Regards. "Tom Ogilvy" wrote in message ... Here is a rough approach. (code is untested) Dim wkbkname As String Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim orderno As String, jobno As String, fName as String If ThisWorkbook.Name = "ABCD.xls" Then 'user has not changed the filename 'get some example data With Range("I9") jobno = .Value If jobno = "" Then jobno = "123" End If End With With Range("J9") orderno = CLng(Mid(.Value, 2, 4)) 'what if "J9" value = "" ....error I think CHECK If orderno = "" Then orderno = "1001" End If End With Application.EnableEvents = False Cancel = True ' put up a message if you want sStr = "E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls" fName = Application.GetSaveASFileName(sStr,"Excel Files (*.xls),*.xls)") if fName < ThisWorkbook.Name and fName < "False" then thisworkbook.SaveAs fName end if Application.EnableEvents = True End If End Sub -- Regards, Tom Ogilvy "Stuart" wrote in message ... I had been trying to do just that, but couldn't get it to work. My code was: Dim wkbkname As String Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim orderno As String, jobno As String If ActiveWorkbook.Name = wkbkname Then 'user has not changed the filename 'get some example data With Range("I9") jobno = .Value If jobno = "" Then jobno = "123" End If End With With Range("J9") orderno = CLng(Mid(.Value, 2, 4)) 'what if "J9" value = "" ....error I think CHECK If orderno = "" Then orderno = "1001" End If End With MsgBox "You MUST save the file with a NEW name" _ & vbNewLine & vbNewLine & _ "Perhaps something like ..." & vbNewLine & _ " " & _ "E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls" Exit Sub End If End Sub I can remind the user, but neither force the Save nor permit the Cancel. I know I should 'Know' this. I have only module code in the ThisWorkbook module, no userforms, buttons etc. Regards and thanks. "Tom Ogilvy" wrote in message ... Why disable the save and saveas buttons. (did you disable Ctrl+S?) Just intercept the save in the Workbook_BeforeSave event and handle the naming there. (cancel the save and manage it yourself) -- Regards, Tom Ogilvy "Stuart" wrote in message ... User opens a workbook containing a single sheet. Code has disabled File Save and Save As. I need to allow the user to Save if they wish! Is the best way a button... if so how, please? I need to ensure the saved name is different from the filename opened, and that they can choose their save directory. If they Cancel then the workbook is not to be saved. Can you help, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.710 / Virus Database: 466 - Release Date: 23/06/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Selection on second workshhet | Excel Worksheet Functions | |||
Can I expand Excel workshhet beyond 65000 rows? | Excel Discussion (Misc queries) | |||
Password protect a workshhet on the net? | Excel Worksheet Functions | |||
workshhet variable | Excel Worksheet Functions | |||
How to pass a workshhet name as a parameter into a subroutine ? | Excel Discussion (Misc queries) |