Makro, shrani kot vsebina celice A1

[tjazma]

Pozdravljeni, v enem od forumov sem zasledil zelo uporaben makro. Z kombunacijo tipk Ctrl + s shrani del. zvezek z trenutnim časom in datumom.
Option Explicit

Sub Shrani()
Dim list As Worksheet

For Each list In ThisWorkbook.Sheets
list.Protect "geslo"
Next

ThisWorkbook.SaveAs "MALA DARILCA" & "TABELA_" & Year(Date) & "-" & _
Month(Date) & "-" & Day(Date) & "+" & Hour(Time) & _
"-" & Minute(Time) & "-" & Second(Time), xlNormal
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo konec:
Cancel = True
Application.EnableEvents = False
Shrani

konec:
Application.EnableEvents = True
End Sub

Moje vprašanje je ali je možno da se del zvezek shrani z imenom vsebine, ki je v celici A1 na primer število 1234

Koda: Izberi vse

[BJ]

Morda bo tole pomagalo.

Koda: Izberi vse

Private Sub CommandButton1_Click()

Dim myfilename As String
Dim resp As Long
myfilename = "C:\Documents and Settings\Administrator\My Documents " & "\" & Range("A1") & ".xls"

resp = vbYes
If Dir(myfilename) <> "" Then
  resp = MsgBox(Prompt:="Ime že obstaja, ali ga želiš prepisati?", Buttons:=vbYesNo)
End If
If resp = vbYes Then
  ActiveWorkbook.SaveCopyAs Filename:= _
        "C:\C:\Documents and Settings\Administrator\My Documents " & "\" & Range("A1") & ".xls"

  MsgBox "Ni shranjeno"
End If

End Sub

Popravite direktorij!
LPB

[admin]

Za shranjevanje DZ s trenutnim časom in datumom, kakor tudi z imenom definiranim v celici A1, ni potrebno vse te kode, temveč je dovolj samo 'SaveAS', a če uporabljate ta makro (ki tudi zaščiti liste), vam bom v njem popravil ustrezen del kode:

Koda: Izberi vse

Sub Shrani()
  Dim list As Worksheet

  For Each list In ThisWorkbook.Sheets
    list.Protect "geslo"
   Next

  ThisWorkbook.SaveAs "MALA DARILCA" & "TABELA_" & Range("a1"), xlNormal
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo konec:
  Cancel = True
  Application.EnableEvents = False
    Shrani

konec:
  Application.EnableEvents = True
End Sub 

[tjazma]

Pozdraljeni,
Najlepša hvala za odgovor makro "opravlja svoje poslanstvo" dela brezhibno.