how to auto assign po numbers in Excel
 

Larry, Thank you for the input. The code did work, but I do have another
small problem. I used the code as a macro and assigned it to a button.
Ideally, we want users to be able to click the button to generate a
po#......which it does, but we also want the cell locked so they cannot
change the number or create their own. When the macro is run a box pops up
asking for a password as the cell has been locked. Is there a way to put
that into the code so the macro will run and not generate that message?

Thank you
Cathy

I don't know what the original code looks like but you probably need to
unprotect the sheet and protect it again in your macro something like:

ActiveSheet.Unprotect Password:="thepassword"
'rest of code
ActiveSheet.Protect Password:="thepassword"

Hope this helps
Rowan

"Cathy Landry" wrote:

Larry, Thank you for the input. The code did work, but I do have another
small problem. I used the code as a macro and assigned it to a button.
Ideally, we want users to be able to click the button to generate a
po#......which it does, but we also want the cell locked so they cannot
change the number or create their own. When the macro is run a box pops up
asking for a password as the cell has been locked. Is there a way to put
that into the code so the macro will run and not generate that message?

Thank you
Cathy

Rowan, this is the code I told her to use:

Private Sub Workbook_Open()
Dim FName As String
Dim FNo As String
Dim x As Long
FName = ThisWorkbook.Path & Application.PathSeparator & "Number.Txt"
FNo = FreeFile
x = 0
On Error Resume Next
Open FName For Input As #FNo
Input #FNo, x
x = x + 1
' *** Change range reference to suit ***
Range("K1").Value = x
Close #FNo
FNo = FreeFile
Open FName For Output As #FNo
Write #1, x
Close #FNo
End Sub


Kathy, you really should have posted this on your original posting so as not
to confuse readers.

Larry

"Rowan" wrote:

I don't know what the original code looks like but you probably need to
unprotect the sheet and protect it again in your macro something like:

ActiveSheet.Unprotect Password:="thepassword"
'rest of code
ActiveSheet.Protect Password:="thepassword"

Hope this helps
Rowan

"Cathy Landry" wrote:

Larry, Thank you for the input. The code did work, but I do have another
small problem. I used the code as a macro and assigned it to a button.
Ideally, we want users to be able to click the button to generate a
po#......which it does, but we also want the cell locked so they cannot
change the number or create their own. When the macro is run a box pops up
asking for a password as the cell has been locked. Is there a way to put
that into the code so the macro will run and not generate that message?

Thank you
Cathy

Thank you all for your help! Is there a class (online) that teaches the
novice in Visual Basic?

Cathy

"Rowan" wrote:

I don't know what the original code looks like but you probably need to
unprotect the sheet and protect it again in your macro something like:

ActiveSheet.Unprotect Password:="thepassword"
'rest of code
ActiveSheet.Protect Password:="thepassword"

Hope this helps
Rowan

"Cathy Landry" wrote:

Larry, Thank you for the input. The code did work, but I do have another
small problem. I used the code as a macro and assigned it to a button.
Ideally, we want users to be able to click the button to generate a
po#......which it does, but we also want the cell locked so they cannot
change the number or create their own. When the macro is run a box pops up
asking for a password as the cell has been locked. Is there a way to put
that into the code so the macro will run and not generate that message?

Thank you
Cathy

You might want to check out "Mr. Excel" at www.mrexcel.com it's a great place
to post questions and problems. They also have some books and videos that
you might find useful.

Larry

"Cathy Landry" wrote:

Thank you all for your help! Is there a class (online) that teaches the
novice in Visual Basic?

Cathy

"Rowan" wrote:

I don't know what the original code looks like but you probably need to
unprotect the sheet and protect it again in your macro something like:

ActiveSheet.Unprotect Password:="thepassword"
'rest of code
ActiveSheet.Protect Password:="thepassword"

Hope this helps
Rowan

"Cathy Landry" wrote:

Larry, Thank you for the input. The code did work, but I do have another
small problem. I used the code as a macro and assigned it to a button.
Ideally, we want users to be able to click the button to generate a
po#......which it does, but we also want the cell locked so they cannot
change the number or create their own. When the macro is run a box pops up
asking for a password as the cell has been locked. Is there a way to put
that into the code so the macro will run and not generate that message?

Thank you
Cathy

Thank you! You have all been very helpful.

"Larry E" wrote:

You might want to check out "Mr. Excel" at www.mrexcel.com it's a great place
to post questions and problems. They also have some books and videos that
you might find useful.

Larry

"Cathy Landry" wrote:

Thank you all for your help! Is there a class (online) that teaches the
novice in Visual Basic?

Cathy

"Rowan" wrote:

I don't know what the original code looks like but you probably need to
unprotect the sheet and protect it again in your macro something like:

ActiveSheet.Unprotect Password:="thepassword"
'rest of code
ActiveSheet.Protect Password:="thepassword"

Hope this helps
Rowan

"Cathy Landry" wrote:

Larry, Thank you for the input. The code did work, but I do have another
small problem. I used the code as a macro and assigned it to a button.
Ideally, we want users to be able to click the button to generate a
po#......which it does, but we also want the cell locked so they cannot
change the number or create their own. When the macro is run a box pops up
asking for a password as the cell has been locked. Is there a way to put
that into the code so the macro will run and not generate that message?

Thank you
Cathy