[RESOLVED] excell questions(help)

[ungas023]

i have a question regarding excell and i have read about si_the_geeks tutorial about excell:
1. how can you make excell.book as a read only file? (with password)
2. how can you make it as an autoformat? i have made some error and made my computer crash several times... thats why i needed your help...

Private Sub CmdPrint_Click()
Dim iRow As Long
Dim iCol As Long
Dim xls As Excel.Application
Dim newcell As String

Set xls = New Excel.Application

xls.Workbooks.Open (App.Path & "\book1")

DoEvents
xls.Visible = True

For iRow = 0 To MSFlexGrid1.Rows - 1
    For iCol = 0 To MSFlexGrid1.Cols - 1
     MSFlexGrid1.Col = iCol
     MSFlexGrid1.Row = iRow
     newcell = Chr(iCol + 65) & iRow + 1
     xls.Worksheets("sheet1").Range(newcell).Value = MSFlexGrid1.Text
    Next
Next
End Sub

[westconn1]

xls.Workbooks.Open (App.Path & "\book1")

unless you saved a workbook as "book1" without extension this will cause a problem, and will anyway as a new excel.application will be called book1, you can only open an existing excel file with workbooks.open
to save the workbook as readonly with password use
xls.activeworkbook.saveas, or better, set a workbook object to work with

SaveAs Method


Saves changes to the sheet (Syntax 1) or workbook (Syntax 2) in a different file.

Syntax 1

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)

Syntax 2

expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout)

expression Required. An expression that returns a Chart or Worksheet object (Syntax 1) or a Workbook object (Syntax 2).

Filename Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don’t, Microsoft Excel saves the file in the current folder.

FileFormat Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.

Password Optional Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.

WriteResPassword Optional Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn’t supplied when the file is opened, the file is opened as read-only.

ReadOnlyRecommended Optional Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.

CreateBackup Optional Variant. True to create a backup file.

AccessMode Optional Variant. The workbook access mode. Can be one of the following XlSaveAsAccessMode constants: xlShared (shared list), xlExclusive (exclusive mode), or xlNoChange (don’t change the access mode). If this argument is omitted, the access mode isn’t changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method.

ConflictResolution Optional Variant. Specifies the way change conflicts are resolved if the workbook is a shared list. Can be one of the following XlSaveConflictResolution constants: xlUserResolution (display the conflict-resolution dialog box), xlLocalSessionChanges (automatically accept the local user’s changes), or xlOtherSessionChanges (accept other changes instead of the local user’s changes). If this argument is omitted, the conflict-resolution dialog box is displayed.

AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.

TextCodePage Optional Variant. Not used in U.S. English Microsoft Excel.

TextVisualLayout Optional Variant. Not used in U.S. English Microsoft Excel

you can also use SetAttr, to set readonly on the file, but only after the workbook is closed

do not set the excel application to visible before loading your grid as it will slow the code as it has to keep updating the screen, show to the user after filling the cells

[ungas023]

wow, very informative, with complete set of strings... good job.... cheers!!!

[ungas023]

can someone tell me how to deactivate the copying of cell from another workbook, tnx...

[Hack]

Moved To Office Development

[fpatano]

can someone tell me how to deactivate the copying of cell from another workbook, tnx...

Do you mean
"Application.CutCopyMode = False"

?

[ungas023]

yep, but it never worked...

[westconn1]

the only way i can think of is to keep monitoring the clipboard and remove anything copied in if it equals the active cell /range

[ungas023]

hmmm.... i will try that.... tnx for all the help... cheers... ^_^

[Siddharth Rout]

Why don't you clear the clipboard like West mentioned. Check this link out to clear the CB.

http://www.vbforums.com/showthread.php?t=449922

[ungas023]

hi, tnx for all of your suggestions, but the problem with this is i can always copy the value of the cell inside it, i am thinking of if he clicks a cell, then the value or picture inside a particular cell it will automatically hide then when he unclick it, it will be visible again..... that way it will be safe to say that he wont get any data from that particular workbook... i am reading alot about excell now.... ^_^

[westconn1]

no, it is pretty well impossible to completely stop someone from copying data from a spreadsheet once it is open, you can do several things to make it more difficult, but i doubt if you can stop someone determined

value or picture inside a particular cell it will automatically hide then when he unclick it, it will be visible again

you can try this in the code window for your sheet, or you can modify for thisworkbook to cover all sheets

Dim rngarr As Variant, addr As String
 
Private Sub Worksheet_Activate()
addr = ActiveCell.Address
rngarr = Range(addr)
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(addr).Value = rngarr
addr = Target.Address
rngarr = Range(addr)
Range(addr).Value = ""
End Sub

you would have to implement something else for pictures

[ungas023]

lol, yeah! nothing beats a determined person, i've tried almost everything and the problem keeps piling up, dont know what to do now.....

and btw, they can cheat into almost anything, even a report with .rpt and access or data report can be cheated to0..... oh boy..... am i making myself paranoid or what?

i will rate this forum as a half resolved.... evrything in this thread is very useful except the copying of workbooks/worksheets....

[westconn1]

you can disable edit menu items (cut and copy), but i am not sure if that will disable the shortcut keys (ctrl c) as well

[ungas023]

hrm, but thats another problem, i need to know this one by one, perhaps i will post it here when i got the code for disabling the ctrl+c.....

[westconn1]

i can disable the menu items copy in both edit and rightclick menus, but ctrl c still works
may be able trap that in keydown event
there are still other menu items to move or copy worksheet, send by email etc etc

[Siddharth Rout]

Seems like my first post went unnoticed... but still here is something which you are looking for... (Disabling Copy/Paste. Also disabling the shortcuts)

'-- Disable Copy Paste
Sub CopyPasteDisable()
    EnableControl 21, False
    EnableControl 19, False
    EnableControl 22, False
    EnableControl 755, False
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "+{DEL}", ""
    Application.OnKey "+{INSERT}", ""
    Application.CellDragAndDrop = False
End Sub

'Enable Copy Paste
Sub CopyPasteEnable()
    EnableControl 21, True
    EnableControl 19, True
    EnableControl 22, True
    EnableControl 755, True
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
End Sub

Sub EnableControl(ByVal lngCommandId As Long, _
ByVal blnEnabled As Boolean, Optional ByVal blnChangeCaption As Boolean)

    On Error Resume Next
    Dim ctlControls As CommandBarControls
    Dim ctlControl  As CommandBarControl
    Dim strCaption  As String        ' Get all matching command controls.
    Set ctlControls = CommandBars.FindControls(ID:=lngCommandId)
    If Not ctlControls Is Nothing Then                ' Get caption.
        strCaption = ctlControls.Item(1).Caption
        If blnChangeCaption = True Then
            If blnEnabled = True Then
                If Right$(strCaption, 9) = " Disabled" Then
                    strCaption = Left$(strCaption, Len(strCaption) - 9)
                End If
            Else
                If Not Right$(strCaption, 9) = " Disabled" Then
                    strCaption = strCaption & " Disabled"
                End If
            End If
        End If
        
        For Each ctlControl In ctlControls
            ctlControl.Enabled = blnEnabled
            ctlControl.Caption = strCaption
        Next
    End If
End Sub

[westconn1]

nice research kool

[ungas023]

lol, nice.... but i want it to discover myself and will ask you guys if i am in a deadlock in my coding, tnx anyway, i will give you a rating too....