Changing an entry from another worksheet

[Guest]

I have a spreadsheet with data that I have protected. The data is entered via
another worksheet then sent over via a macro. I have some else using this and
really don't want them messing with the database spreadsheet. My problem is
if they make an incorrect entry how can I pull that entry back out again for
them to change and then resubmit. Can I write a macro for this maybe using an
invoice number as an identifier? I would greatly appreciate any direction.

I know this sounds like something for Access. Long story why not! Thank you.

 

[Dave Peterson]

You may want to look at Data|Form. It has options that can do some of what you
want.

But if you really want more control (and not allow the user to access the real
data), I think you'll need a userform--kind of like the one John Walkenbach has
at:
http://j-walk.com/ss/dataform/index.htm

And if you want to learn a bit about userforms, visit Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html

 

[Guest]

Thank you Dave.
Yeah I don't want to use Data\Form because I really don't want them to have
full access to that data. And the way I understand the forms it only works on
the worksheet. I will check out the sites you suggested. Thank you!

You may want to look at Data|Form. It has options that can do some of what you
want.

But if you really want more control (and not allow the user to access the real
data), I think you'll need a userform--kind of like the one John Walkenbach has
at:
http://j-walk.com/ss/dataform/index.htm

And if you want to learn a bit about userforms, visit Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html

 

[Dave Peterson]

I put some data on Sheet1 (in A1:G25 -- just test data).
I protected sheet1 with a password (hi) and then hid sheet1.

I put a button from the forms toolbar on Sheet2.

And assigned that button a this macro:

Option Explicit
Sub testme()

Dim wks As Worksheet
Set wks = Worksheets("Sheet1")
With wks
.Unprotect Password:="hi"
Application.DisplayAlerts = False
.ShowDataForm
Application.DisplayAlerts = True
.Protect Password:="hi"
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Thank you Dave.
Yeah I don't want to use Data\Form because I really don't want them to have
full access to that data. And the way I understand the forms it only works on
the worksheet. I will check out the sites you suggested. Thank you!