Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Collecting data from different sheet
Hi Everyone
I have 5 sheets example sht1,sht2,sht3,sht4,sht5, 4 sheets is reserved for entry (Sht1-Sht4) and sht5 Accumulate the data from all these sheets Now I have 6 entries in sheet1 start from Columns B1 to B6 Same for sht 2 8 entries from Columns B1 to B8 Same for sht 3 9 entries from Columns B1 to B9 Same for sht 2 3 entries from Columns B1 to B3 Sheet 5 will collect this info in this order start from sheet 1 then 2,3,4 sheet5 will be like this: Columns b1 to b6 info from sheet1, Columns b7 to b14 info from sheet2,Columns b15 to b23 info from sheet3 and Columns b24 to b26 info from sheet4 What I need now if i input any data on sheet1 in the last empty cell in columnB that is B7 i need that the info goes in sheet 5 on b7 and continue like this if i insert info in last empty cell is sheet2 that is B9 he info goes with same sequence and at the end if i have completed 90 entries on sheet 1 this 90 entries goes on sheet5 as follow B2 to B91 followed by info from sheet 2 same scanario and sht3 ,4 Thanks for ur help Bakar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Several Password
Bakar,
This might work, although you'll want to password protect the code for the userform before circulating your final workbook. The idea of this series of macros is that all but one sheet are "veryhidden" when the file closes. The one visible sheet will have a message that alerts users to ensure macros are enabled, and may also include a button for unlocking the workbook (the code of which would simply be Userform1.Show). Once you have this first sheet set up, go to the "ThisWorkbook" module and paste the following: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.CodeName = "Sheet1" Then ws.Visible = True Else ws.Visible = xlSheetVeryHidden End If Next ws End Sub Private Sub Workbook_Open() UserForm1.Show End Sub Then, create a Userform with two textboxes (username and password) and two buttons (OK and Cancel). On the password textbox, set the PasswordChar Property to "*" or another masking character if you wish. Then, in the Userform module, paste this code: Private Sub CommandButton1_Click() Dim i As Long Dim ws As Worksheet Dim sPWord(1 To 5) As String Dim sUser(1 To 5) As String sUser(1) = "Test" sPWord(1) = "Testme" 'fill in others as well... Select Case TextBox1 Case sUser(1) i = 1 Case sUser(2) i = 2 Case sUser(3) i = 3 Case sUser(4) i = 4 Case sUser(5) i = 5 Case Else MsgBox "Invalid User ID or Password" Exit Sub End Select If TextBox2 = sPWord(i) Then For Each ws In Worksheets If ws.CodeName = "Sheet1" Then 'Do nothing Else ws.Visible = True End If Next ws Sheet1.Visible = xlSheetVeryHidden Unload Me Else MsgBox "Invalid User ID or Password" End If End Sub Private Sub CommandButton2_Click() Unload Me End Sub The first few variables will be the usernames and passwords. If the textbox info matches a username and its corresponding password, all sheets will be made visible except for Sheet1, which will become "VeryHidden". If the entries do not match, the users will receive an error message. You can customize this code from here, but I think this will get you started. Ben |
#3
|
|||
|
|||
Quote:
BUT I need to give access to one user only sheet 2 and now all sheets are opened I must give to different user different sheet to work Example user 1 sheet3 only remaining sheet is hidden etc Thnxs again Bakar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
collecting fata from all sheets into one sheet | Excel Worksheet Functions | |||
Collecting data from excel forms into one spread sheet | Excel Programming | |||
collecting data from many sheets to one sheet | Excel Discussion (Misc queries) | |||
collecting data from one sheet to another | Excel Discussion (Misc queries) | |||
Collecting the names from other sheet for print | Excel Discussion (Misc queries) |