Real hard one

  • Thread starter Thread starter PANCAKE
  • Start date Start date
P

PANCAKE

Hey,

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

The second thing i want to know is how can I let collum A be the sam
as collum A in a other worksheet in the same excel file whitout havin
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 ad
some kind of printer settings so it wil not print a lot of paper fo
nothing.

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

Thanks for helping me out.

Greets Pancak
 
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 thi
code
Private Sub Worksheet_Activate()
Password = InputBox("Please insert password to view data.", "Passwor
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 thi
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 littl
button on the toolbar
- and insert this line
call module1.macro1


there she does it all done
hope this help
 
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
 
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)
 
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.
 
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.
 
alread i will send on monday morning belgium time the test version of
the document.

How can i send it to you?
 
Back
Top