Real hard one

[PANCAKE]

Hey,

I'm looking to get my worksheets secured in that way that only a person who have the correct loggin on the pc can open the worksheet.(the tab page in the left lower corner)

The second thing i want to know is how can I let collum A be the same as collum A in a other worksheet in the same excel file whitout having to ad the formule =Tab1!A1 an than copying it over the entire A collum. becausse that result in a 0 to evry cel in collum A what force me to add some kind of printer settings so it wil not print a lot of paper for nothing.

Third question: How can i make it possible that i have one button to insert somthing in Collum A and it automaticly set it to al the Tab pages (3 pages)

Thanks for helping me out.

Greets Pancake

[Zygan]

question 1 secure.

You will probably never get them secure but this will stop the average

in vba editor select the sheet you want to protect then insert this code
Private Sub Worksheet_Activate()
Password = InputBox("Please insert password to view data.", "Password Checker")
If Password = "insert your password here" Then
'do nothing
Else
Sheets ("insert the sheet name you want it to return to here").select
End If
End Sub


question 2 and 3 in one
open vba editor and insert a module into your workbook and paste this macro
Sub Macro1()
Sheets("sheet1").Select
Columns("A:A").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet3").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet4").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'etc
'etc
'as many sheets you want or have
' please change the sheet names in the sheets("").select part
End Sub



two options make a button usind

A FORM BUTTON

- right click and asign macro asign macro1


OR


a CONTROL TOOLBOX BUTTON

- double click button after going into deign mode which is a little button on the toolbar
- and insert this line
call module1.macro1


there she does it all done
hope this helps

[Zygan]

question 1 secure.

You will probably never get them secure but this will stop the average

in vba editor select the sheet you want to protect then insert this code
Private Sub Worksheet_Activate()
Password = InputBox("Please insert password to view data.", "Password Checker")
If Password = "insert your password here" Then
'do nothing
Else
Sheets ("insert the sheet name you want it to return to here").select
End If
End Sub


question 2 and 3 in one
open vba editor and insert a module into your workbook and paste this macro
Sub Macro1()
Sheets("sheet1").Select
Columns("A:A").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet3").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet4").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'etc
'etc
'as many sheets you want or have
' please change the sheet names in the sheets("").select part
End Sub



two options make a button usind

A FORM BUTTON

- right click and asign macro asign macro1


OR


a CONTROL TOOLBOX BUTTON

- double click button after going into deign mode which is a little button on the toolbar
- and insert this line
call module1.macro1


there she does it all done
hope this helps

[Bob Phillips]

First part

Tools>Protect Workbook

Second part

=IF(Tab1!A1="","", Tab1!A1 )

Third part

I don't understand

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PANCAKE" <[email protected]> wrote in
message news:[email protected]...
>
> Hey,
>
> I'm looking to get my worksheets secured in that way that only a person
> who have the correct loggin on the pc can open the worksheet.(the tab
> page in the left lower corner)
>
> The second thing i want to know is how can I let collum A be the same
> as collum A in a other worksheet in the same excel file whitout having
> to ad the formule =Tab1!A1 an than copying it over the entire A collum.
> becausse that result in a 0 to evry cel in collum A what force me to add
> some kind of printer settings so it wil not print a lot of paper for
> nothing.
>
> Third question: How can i make it possible that i have one button to
> insert somthing in Collum A and it automaticly set it to al the Tab
> pages (3 pages)
>
> Thanks for helping me out.
>
> Greets Pancake
>
>
> --
> PANCAKE
> ------------------------------------------------------------------------
> PANCAKE's Profile:
http://www.excelforum.com/member.php...o&userid=36157
> View this thread: http://www.excelforum.com/showthread...hreadid=559342
>

[PANCAKE]

thanks i will test it out

greets pancake

[PANCAKE]

alread thanks zygan for the first solution its really great that one. a litle question aboutit can you also make that they can not see the page?

and about the second and third question i get an error(becausse the cels are not the same) i ment somthing more in the way like (its not rwiten in vba code its just the logical part im putting here

select sheet one insert a row between line 6 and 7
then reply this on tab two and three and the thrikky part is that when i do it it have to be like i click the button insert new row it have to do it in the three sheets and the data i enter in the one sheet have to be the same in the other two bud only for collum A bud the data at collum A starts at the fith row and the first two rows are combined and the third and fourth are also combined if you want a example ill upload the file on monday its just htat i want to have a high end user excel workblad that is configered in htat way htat one you are working whit different people on it nowbody can mess up bu d only can give rwong information bud than its their problem and not hte problem of the one that is responcible for the end data. i could do it by giving evrybody a different file and a ohter file that is linking to the files to get al the data but that is just like putting a lot of files on the server and people have big change to mess it up like deleting the file deleting formules and evrything they can do so im going to try to make a start page whit buttons so they cant do nothing else bud putting data in it and save it.

[Zygan]

to prevent a user from not seeing a sheet

make a "home page" and insert a button and put this code in very simliar to before but it wont change the page untill the password is right

Private Sub commandbutton1_click()
Password = InputBox("Please insert password to view data.", "Password Checker")
If Password = "insert your password here" Then
Sheets ("insert the sheet name you want it to return to here").select
else
msgbox("incorrect password")
End If
End Sub


i would be eager to see a test version of your spreadsheet so i can have a look at what you mean




the error you are getting is easy solved replcae this line Columns("A:A").Select
with this line
range("A1:65500").select

should fix it but you cannot have a button without vb code to make it do any good anyways.

[PANCAKE]

alread i will send on monday morning belgium time the test version of the document.

How can i send it to you?