excel process problem 2

[tango]

thanks alot sharad. few things i need yr further explanation.

1) for point no 1, you mean i need to do import in vbe for the first
time? when i export or import, thisworkbook and sheets the type is
cls. what is class?

2) for point no 2, what do you mean by template? do you mean make a
copy of the workbook and clear the contents and remain the vba?

3) any vba example of password box when open the workbook and the
password is not hardcode in vba.


thanks alot





1) For the existing project since the excel files are already created
on
each machine,
you can do import on each file.

2) For the next project, you can create a template having your VBE
code in
it and circulate to
every one. For the next projects, they should use this template .

3) Instead of adding command button in a sheet, add a new button on
the tool
bar,
and assing the macro to it. So the button will be always available to
the
use on the tool bar.

Transfering data from all worksheets:

Below code gets a valid sheet no. from the user and transfers contents
of
Range("A1") from every sheet (except the sheet for sheet no. entered )
in
to
the sheet for sheet no. entered, in to Column F.

Sub Test()
Dim getNum As String, mySheetNum As Integer
Dim myRange As Range, nCount As Integer
Dim totSheetNum As Integer

getSheetNum:
getNum = InputBox("Please enter the desitantion sheet number", _
"Sheet Numbar Required:")
If Len(getNum) = 0 Then Exit Sub
'i.e. if no data entered or user presses cancel, do noting.
If Not IsNumeric(getNum) Then
MsgBox "Please enter an Integer"
GoTo getSheetNum:
End If
'i.e. check if data entered is numeric or not if no ask again
If InStr(1, getNum, ".", vbTextCompare) > 0 Then
MsgBox "Please enter an Integer"
GoTo getSheetNum:
End If
'i.e check if the number entered has a "." in it if yes ask
'for an integer again

mySheetNum = Int(getNum)
totSheetNum = ThisWorkbook.Sheets.Count
If mySheetNum > totSheetNum Then
MsgBox "The sheet number you entered is not valid because" & _
" there are only " & totSheetNum & " sheets!" & Chr(13) & _
"Please enter a valid sheet number."
GoTo getSheetNum
End If

nCount = 1
For Each s In ThisWorkbook.Sheets
If Not s.Index = mySheetNum Then
Sheets(mySheetNum).Range("F" & nCount) = s.Range("A1")
nCount = nCount + 1
End If
Next

End Sub

 

[Sharad Naik]

1) a)Yes, for the first time. Then save the file immediately after import
and the code will be saved in to those files.
b) ThisWorkBook or Sheet1 etc. are Class Modules. Better way to transfer
these are instead of exporting and importing, copy the entire code in to
Thisworkbook and paste in to a text file. Open the text file on other PC,
select all, copy and paste in to the empty Thisworkbook procedure.
Same for Sheet1 etc.

2) Yes. Keep the code, do clear all contents, and then save the file as
template (i.e. .xlt). e.g. Project-xyz.xlt.
Then copy the this template Project-xyz.xlt in to default template path
location on each PC,
so that when user click on File menu and select 'New', he will see
Project-xyz.xlt as template, and can
select it when preparing new Project.

3)
Attached please find files pwdForm.frm pwd and Form.frx (a userform)
and Public_Declare.bas (a module where public variables are declared.)
Save these 3 files in to a new folder.
Start a new workbook, go to VBE. Import the files pwdForm.frm and
Public_Declare.bas
in to it. (pwdForm.frx can not be imported but it is needed in the same
folder to
inmport pwdForm.frm)

Then in ThisWorkBook class paste below code.

Private Sub Workbook_Open()
Application.EnableCancelKey = xlDisabled
userGaveUp = True
userPwdOK = False
Beep
pwdForm.Show
If userGaveUp Or Not userPwdOK Then
MsgBox "The workbook will close", vbCritical, "Password not etered:"
ThisWorkbook.Close
Exit Sub
End If
If userPwdOK Then
MsgBox "The password is correct. You can use this workbook now.", _
vbInformation, "Welcome to " & ThisWorkbook.Name
End If
End Sub

Save the workbook and close it.

Open it again and see what happens.
The password is tango

open the workbook with password. Go to visuale basic editor.
Go to the Form pwdForm. Double click on the 'OK' command button
to view it's code. You can change user password in the declaration
Const myPWD = "tango" to what ever you like.

Sharad