excel vba - 2 problems

C

chief

i have set it up so that when they click a command button i
automatically saves the invoice sheet into my documents with the K
cell contents as the file name. However, i would like to have it set u
so that one they close, the next time they open the sheet to do anothe
invoice it will be 1 number higher than the last saved invoice number
The reply posted before took care of most of that, but now it seem
that when i click the command button to auto save it to my docs an
close the sheet, when i open a new sheet it comes up as the sam
number. Should there be some sort of loop code in there?

code: With Sheets("sheet1")
.Range("K5").Value = .Range("K5").Value + 1
End With


Second problem is when i have a userform show and there are two comman
buttons that can be clicked inside of it, how can i set it up so tha
commandbutton2 does not appear until commandbutton1 is clicked on?

is that possible? I tried some load and hide codes but my knowledge i
limited

thank
 
J

JulieD

Hi

on your first question:
could you copy & paste ALL the command button code to save and close the
sheet - it sounds like the close is happening before the number is being
updated.

on your second question:
you can use the visible property of the command button
in design view set the visible property to false for commandbutton2 and then
in commandbutton1's onclick code type
commandbutton2.visible = true

Cheers
JulieD
 
A

AlfD

Hi!

Or you could do the job with just one button if that suited you. Chang
the caption according to its function at the time and read the captio
property to check which mode it is in.

Al
 
C

chief

thanks for the help julie, i actually made the 2 command buttons int
one to make it easier. It is now set up as

Private Sub CommandButton1_Click()
Sheet1.SaveAs FileName:="H:\MyDocs\" & Range("K5").Value
Range("A1:M51").PrintOut

End Sub

What code can be used in order to close the whole sheet once th
save/print command button is clicked. I tried to do a sort o
sheet1.hide but that doesn't allow it, any ideas? Why would it be tha
the invoice numbering is not working properly
 
J

JulieD

Hi chief

you can't actually close a sheet - you can only close the workbook and this
can be done with the following line of code

ActiveWorkbook.Close

if you click on the word "close" once you've typed it into your code and
press F1 you can find out about the other parameters that it takes.

let us know how you go

Cheers
JulieD
 
C

chief

Thanks for helping me straighten that out, but i am still running int
trouble with this dang invoice numbering. This is what i have and s
far it doesn't work properly

in sheet1 i have:

Private Sub CommandButton1__Click()
UserForm1.Show
End Sub


In this workbook i have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("K5").Value = Range("K5").Cells + 1

End Sub

Private Sub Workbook_Open()
Range("K3") = Now()
Range("K3").NumberFormat = "mmmm d, yyyy"
Range("K5").NumberFormat = "0000"
If Range("L8").Value >= 1 Then
Range("K5").Value = Range("L8").Value + 1
End If
End Sub


In the userform i have:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode A
Integer)
If CloseMode <> 1 Then Cancel = 1

End Sub


Private Sub CommandButton1_Click()
Sheet1.SaveAs FileName:="H:\MyDocs\" & Range("K5").Value
Range("A1:M51").PrintOut
ActiveWorkbook.Close

End Sub


my goal is to set it up so that when they click on the shortcut to th
Invoice_Retail file, the invoice number will come up for the very firs
time as 0001. They will enter the info, click the command button fo
save/print/close. This will save the sheet as 0001 in my document
location. They can then click on the shortcut again to start wit
0002, and so on...or they can revisit the 0001 saved file in my doc
for recall information purposes. Is there maybe a way to save only th
K5 (invoice number) cell in the template sheet so that when the shee
is opened the next time it will read that number and increment it b
0001, and hopefully not save the rest of the information in th
template sheet such as customer name, etc.? I just want to have
sheet which will open as a new number everytime, and save it to
location, and then be able to open another sheet and have it be
number higher than the last. I have been racking my brain trying t
get it to work, please help before i go craz
 
J

JulieD

Hi Chief

the reason its not working is that you're not saving the invoice number ever
....what you need to do is SAVE the workbook in the
Private Sub CommandButton1_Click() code
ActiveWorkbook.Save
Sheet1.SaveAs FileName:="H:\MyDocs\" & Range("K5").Value
Range("A1:M51").PrintOut
ActiveWorkbook.Close
End Sub

then add in some workbook_open code (in the ThisWorkbook module) that clears
the contents of all the cells that you don't want stuff saved in (excluding
K5), move the invoice number incrementing code to here as well

e.g.
Range("A1:A10").clearcontents

Hope this helps
Cheers
JulieD
 
C

chief

JulieD you are an absolute genius!

that is now working great.

This is the last time I'll bug you, when it does save to my docs as i
is doing, if i go into my docs and open 0001 for example, it will go u
to the next number? is there maybe a way to lock that cell or th
sheet once it has saved itself in my docs?

once again thank you a millio
 
J

JulieD

Hi Chief

sorry ... didn't think about the code running when the other books are open
.... so how about this idea:

change the
Private Sub CommandButton1_Click() code
Sheets("sheet1").Copy
ActiveWorkbook.SaveAs FileName:="H:\MyDocs\" & Range("K5").Value
ActiveWorkbook.Close
Range("A1:M51").PrintOut
Range("k5") = Range("k5") + 1
ActiveWorkbook.Close True
End sub

and take the incrementing of the number out of the "this workbook" code.

Cheers
JulieD
 
C

chief

hello Julie

thanks for helping me out so far. I tried the code you mentioned ye
when i attempt to click the command button it shows an error reading:
"Copy Method of Worksheet class failed"??

any ideas
 
J

JulieD

Hi Chief

no, not really :)

when you press the DEBUG button is a line of code highlighted?

do you want to zip up your workbook & email it to me direct and i'll take a
look at it ...
PS what version of excel are you using?

Cheers
JulieD
julied_ng at hcts dot net dot au
 
Top