New numbers

[Larry]

Hi folks, I appreciate you being there to help. I am a novice so need lots of
help with this stuff.

I have a workbook that I use to generate a new number each time it is
opened. The number is used to index descrepencies on autos in a managed fleet.
It looks like this in "thisworkbook" :

Private Sub Workbook_Open()
Columns("B:IV").Select
Selection.EntireColumn.Hidden = True
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.Value = Selection.Offset(-1, 0).Value + 1
MsgBox "To copy this number hold the Ctrl and C keys together. The new
number box buzzes. Don't close this page yet. Click on the waiver card tab at
the bottom of the window to open it again. Hold down the Ctrl and V keys
together, the new number will be entered in the new waiver block you had
selected. Now you can go to bottom of the window and right click the New
Number page and select close: NOTE:You have to close this before starting!"
ThisWorkbook.Save
End Sub


The user: drivers, click a hyperlinked cell on a sheet designated for a
vehicle, then copy and paste the new generated numberfrom the opened workbook
into the target cell in a different workbook.
Oh how I wish there was a way to have the active workbook target cell just
enter the new number when it is clicked. No more cut and paste which confuses
some of the users. Any takers on this one? thanks, larry

 

[Richard Buttrey]

How are you defining the target cell in the second workbook?

Is it a named cell, or a cell which has a varying position, e.g. the
next blank cell in a column of invoice numbers?

It's not immediately apparent why you need this first workbook. Is it
perhaps that it's used to generate the next invoice number, which may
be used by other applications. i.e. the invoice numbers in your
managed fleet workbook are not necessarily being incremented by 1 each
time.

Regards

Hi folks, I appreciate you being there to help. I am a novice so need lots of
help with this stuff.

I have a workbook that I use to generate a new number each time it is
opened. The number is used to index descrepencies on autos in a managed fleet.
It looks like this in "thisworkbook" :

Private Sub Workbook_Open()
Columns("B:IV").Select
Selection.EntireColumn.Hidden = True
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.Value = Selection.Offset(-1, 0).Value + 1
MsgBox "To copy this number hold the Ctrl and C keys together. The new
number box buzzes. Don't close this page yet. Click on the waiver card tab at
the bottom of the window to open it again. Hold down the Ctrl and V keys
together, the new number will be entered in the new waiver block you had
selected. Now you can go to bottom of the window and right click the New
Number page and select close: NOTE:You have to close this before starting!"
ThisWorkbook.Save
End Sub


The user: drivers, click a hyperlinked cell on a sheet designated for a
vehicle, then copy and paste the new generated numberfrom the opened workbook
into the target cell in a different workbook.
Oh how I wish there was a way to have the active workbook target cell just
enter the new number when it is clicked. No more cut and paste which confuses
some of the users. Any takers on this one? thanks, larry

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Larry]

Hi Richard, thanks for the reply.
the cell I wnat to have the new number in is always the next cell in the
column. i.e.
Driver opens the workbook 2002 and selects sheet 02b2000 which corresponds
to a certain vehicle. column A is the dynamic list for descrepencies; user
can select from the drop down or write their own descrep, shich will be added
to the bottom of the list. column B is where the new, unique number goes for
that descrep. column C is the date which autofills on selection and column D
is for the driver initials taken from a static validation list.
So, the trick is to get the nex empty cell in column B"waiver no's" to :
1- Generate the next highest number for all workbooks and sheets so it is
unique and not used again.
2-
go and get the next new number and place it in the next empty selected cell.

Is this possible to do automatically so drivers won't have to go to the new
number workbook and cut and paste? thanks richard.

 

[Richard Buttrey]

Hope I've understood the requirement.

One solution.

Create a single sheet workbook called "InvoiceNumber.xls" and name
cell A1 "InvNo"
The only purpose of this workbook is to hold the next number. Save it.

In your workbook 2002 add a "Variables" sheet - (you may already have
one which holds other variables), and again name a cell "InvNo"

When you say the user clicks a hyperlinked cell I wasn't quite sure
what you meant, since a hyperlink would just take you somewhere else.
In this solution create a Command Button on each of the discrepancies
sheet in your Wb and set the caption property name to "Add Invoice
Number".

In the Click event of the Button View code VBA window enter the
following:

Private Sub CommandButton1_Click()
Call AddInvoice.OpenInvWb
End Sub

Add a module and name it "AddInvoice"
Now copy the procedure below into the AddInvoice module

When the usre clicks the Command button, it opens the "InvoiceNumber"
workbook, sets the iInvNo variable to the current Inv No, increments
the Invoice No in the InvoiceNumber workbook and re-saves it.

It then puts the iInvNo variable into the "InvNo" cell in the 2002
workbook.

It then confirms with the user via a Message box that they really do
want to add the new number, and if they decline it re-opens the
InvoiceNumber workbook and decreases the InvNo by 1.

If they accept, the number will be copied to the next available blank
cell in Column B

Sub OpenInvWb()
Dim TempWb As Workbook, iInvNo As Integer, iAnswer As Integer
Application.ScreenUpdating = False
Workbooks.Open ("InvoiceNumber")
Set TempWb = ActiveWorkbook
Range("Invno") = Range("Invno") + 1
iInvNo = Range("invno")
TempWb.Save: TempWb.Close
Range("Invno") = iInvNo

iAnswer = MsgBox("You are about to add Invoice Number " _
& Range("Invoicenumber") & Chr(13) _
& " Is this OK?", vbYesNo)
If iAnswer = 7 Then
Workbooks.Open ("Invoicenumber")
Set TempWb = ActiveWorkbook
Range("Invno") = Range("Invno") - 1
iInvNo = Range("invno")
TempWb.Save: TempWb.Close
Range("Invno") = iInvNo
Exit Sub
End If
ActiveSheet.Range("B65536").End(xlUp).Offset(1, 0) =
Range("invno")
Application.ScreenUpdating = True

End Sub


As an alternative to putting Command Buttons on all of your various
sheets, and particularly if you have a lot, or you have other tasks
that could sensibly be done via a menu, it would be preferable to set
up a user form "MyForm" containing a command button and display the
userform with a procedure that's called by a macro shortcut - say
CTRL-A for "Add" - which points to the "DisplayForm" procedure.

Sub DisplayForm
Myform.Load
End Sub


HTH.
Re-post if it's not clear.

Hi Richard, thanks for the reply.
the cell I wnat to have the new number in is always the next cell in the
column. i.e.
Driver opens the workbook 2002 and selects sheet 02b2000 which corresponds
to a certain vehicle. column A is the dynamic list for descrepencies; user
can select from the drop down or write their own descrep, shich will be added
to the bottom of the list. column B is where the new, unique number goes for
that descrep. column C is the date which autofills on selection and column D
is for the driver initials taken from a static validation list.
So, the trick is to get the nex empty cell in column B"waiver no's" to :
1- Generate the next highest number for all workbooks and sheets so it is
unique and not used again.
2-
go and get the next new number and place it in the next empty selected cell.

Is this possible to do automatically so drivers won't have to go to the new
number workbook and cut and paste? thanks richard.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Larry]

Hi richard,
thanks for the help. I am having some trouble with it though. I placed the
button1 code as you said but it resides with other code and is not playing
well. Here is what I have for the descrepency sheet code so far:

Private Sub CommandButton1_Click()
Call AddInvoice.OpenInvWb
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 1 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value

End If
End If

End Sub

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Automatically inserts today's date in cell in column C when selected
'if the cell was empty. Does not overwrite occupied cell.
If ActiveCell.Column = 3 Then 'Limits macro action to column C
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
Selection.Value = Date 'Insert today's date in Target cell
End If
Else
End If
End Sub

I am getting runtime error 1004 saying the InvoiceNumber.xls workbook cannot
be found. I am sure I do not have a workbook named as such. Everything
appears to be as you indicated but I continue to get an error. the workbooks
are in the same directory. Any suggestions? thanks for the help! larry




*****************************************

 

[Larry]

I don't know why I wrote that I did not have an "Invoice Number.xls" since
that is the firts thing I created. I have been trying different things but
still get the eror 1004. Perhaps you will see something in the way I have the
code written in the above note. I have very limited knowledge of VB and have
lots of help from this web page. I appreciate your time and experience on
this. take care, larry

 

[Richard Buttrey]

Larry,

It's a little difficult to debug since I don't really know the layout
of your WB, nor what the range name "NameList" contains.

If you'd like to send me the WB in question - anonymised if necessary
if the data is confidential, then I'd be happy to take a look and bolt
on my suggested code. Just let me have a few notes about how you
expect it to work.

Remove the ".nospam.removethis" from my email name if you do this

Rgds

Hi richard,
thanks for the help. I am having some trouble with it though. I placed the
button1 code as you said but it resides with other code and is not playing
well. Here is what I have for the descrepency sheet code so far:

Private Sub CommandButton1_Click()
Call AddInvoice.OpenInvWb
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 1 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("NameList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value

End If
End If

End Sub

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Automatically inserts today's date in cell in column C when selected
'if the cell was empty. Does not overwrite occupied cell.
If ActiveCell.Column = 3 Then 'Limits macro action to column C
If ActiveCell.Value = "" Then 'Check to see if Target cell empty
Selection.Value = Date 'Insert today's date in Target cell
End If
Else
End If
End Sub

I am getting runtime error 1004 saying the InvoiceNumber.xls workbook cannot
be found. I am sure I do not have a workbook named as such. Everything
appears to be as you indicated but I continue to get an error. the workbooks
are in the same directory. Any suggestions? thanks for the help! larry




*****************************************

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

Larry,

I've returned the updated workbooks. The email address you used to
send them to me seems to bounce, so I've just tried your gmail.co,
address below.

Let me know if they don't get through

Rgds

I don't know why I wrote that I did not have an "Invoice Number.xls" since
that is the firts thing I created. I have been trying different things but
still get the eror 1004. Perhaps you will see something in the way I have the
code written in the above note. I have very limited knowledge of VB and have
lots of help from this web page. I appreciate your time and experience on
this. take care, larry

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________