Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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 On Fri, 25 Aug 2006 08:35:28 -0700, Larry wrote: 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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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" wrote: 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 On Fri, 25 Aug 2006 08:35:28 -0700, Larry wrote: 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 __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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. On Fri, 25 Aug 2006 11:25:02 -0700, Larry wrote: 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" wrote: 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 On Fri, 25 Aug 2006 08:35:28 -0700, Larry wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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("Na meList"), 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" wrote: 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. On Fri, 25 Aug 2006 11:25:02 -0700, Larry wrote: 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" wrote: 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 On Fri, 25 Aug 2006 08:35:28 -0700, Larry wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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 "Larry" wrote: 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("Na meList"), 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" wrote: 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. On Fri, 25 Aug 2006 11:25:02 -0700, Larry wrote: 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" wrote: 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 On Fri, 25 Aug 2006 08:35:28 -0700, Larry wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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 On Tue, 29 Aug 2006 06:21:01 -0700, Larry wrote: 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("Na meList"), 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" wrote: 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. On Fri, 25 Aug 2006 11:25:02 -0700, Larry wrote: 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" wrote: 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 On Fri, 25 Aug 2006 08:35:28 -0700, Larry wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New numbers
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 On Wed, 30 Aug 2006 05:38:02 -0700, Larry wrote: 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 "Larry" wrote: 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("Na meList"), 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" wrote: 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. On Fri, 25 Aug 2006 11:25:02 -0700, Larry wrote: 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" wrote: 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 On Fri, 25 Aug 2006 08:35:28 -0700, Larry wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching numbers in Worksheet? | Excel Worksheet Functions | |||
conditional formatting | Excel Worksheet Functions | |||
How to generate sets of random numbers without having duplicates | Excel Worksheet Functions | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) |