Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel process problem 2
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 "tango" wrote in message om... Dear All, I write a vba program to process a task and this program is sitting in a workbook for each of the staff about 20 in the same department. Each of them cannot see each other's workbook due to the nature of their work but the process step is the same for all. 1) May I know how should I do to rollout the vba program to each of their workbook? Do I need to do the import in the VBE? 2) they are using a workbook for each of the project. When they do the subsequently project they need a new workbook to do the same process again. If that is so may I know if I need to do the import all the time when they use new workbook for new project? I cannot let them do as they will access to the vba source. 3) there is a process where they need to transfer data from worksheets to 1 particular worksheet to derive the subtotal. I created a toolbar commandbutton to process. The problem is they need to go to the specific or active sheet to process. If having 60 sheets then they need to process 60 times. I do not want to put the button on sheets as will create a lot of procedures. Any other way to process by providing sheet number in inputbox and process once for all the sheets(transfer data from all the source sheets to a single destination sheet) What is the vba statement for this? Again, thanks for the assistance so far. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel process problem 2
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 "tango" wrote in message om... 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 "tango" wrote in message om... Dear All, I write a vba program to process a task and this program is sitting in a workbook for each of the staff about 20 in the same department. Each of them cannot see each other's workbook due to the nature of their work but the process step is the same for all. 1) May I know how should I do to rollout the vba program to each of their workbook? Do I need to do the import in the VBE? 2) they are using a workbook for each of the project. When they do the subsequently project they need a new workbook to do the same process again. If that is so may I know if I need to do the import all the time when they use new workbook for new project? I cannot let them do as they will access to the vba source. 3) there is a process where they need to transfer data from worksheets to 1 particular worksheet to derive the subtotal. I created a toolbar commandbutton to process. The problem is they need to go to the specific or active sheet to process. If having 60 sheets then they need to process 60 times. I do not want to put the button on sheets as will create a lot of procedures. Any other way to process by providing sheet number in inputbox and process once for all the sheets(transfer data from all the source sheets to a single destination sheet) What is the vba statement for this? Again, thanks for the assistance so far. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using excel to process QIF file | Excel Discussion (Misc queries) | |||
What process is performed behind excel? | Excel Discussion (Misc queries) | |||
excel process problem | Excel Programming | |||
Problem with process Excel | Excel Programming | |||
to kill a excel process | Excel Programming |