Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancing A Number With The Push A Button
Greeting,
I tried this in the misc group, but I think that was the wrong group, so here goes, again.... I have an invoice that uses a button to advance the invoice number by 1. When I wrote it the numbers were just numbers, but since then a letter has been added to the front of the number (was 17445 now D-017445). My advancing code no longer works and I don't know why! Here is the code: Private Sub GetNextInv_Click() Range("InvNoLast").Value = Range("InvNoLast").Value + 1 End Sub Anyone have an idea on how to fix it? Any help would be most appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancing A Number With The Push A Button
Try something like
Private Sub GetNextInv_Click() Dim S As String Dim N As Long Dim Pos As Integer S = Range("InvNoLast").Value Pos = InStr(1, S, "-") N = CLng(Mid(S, Pos + 1)) N = N + 1 Range("InvNoLast").Value = Left(S, Pos) & Format(N, "000000") End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Minitman" wrote in message ... Greeting, I tried this in the misc group, but I think that was the wrong group, so here goes, again.... I have an invoice that uses a button to advance the invoice number by 1. When I wrote it the numbers were just numbers, but since then a letter has been added to the front of the number (was 17445 now D-017445). My advancing code no longer works and I don't know why! Here is the code: Private Sub GetNextInv_Click() Range("InvNoLast").Value = Range("InvNoLast").Value + 1 End Sub Anyone have an idea on how to fix it? Any help would be most appreciated. TIA -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancing A Number With The Push A Button
You got a couple of replies there.
Minitman wrote: Greeting, I tried this in the misc group, but I think that was the wrong group, so here goes, again.... I have an invoice that uses a button to advance the invoice number by 1. When I wrote it the numbers were just numbers, but since then a letter has been added to the front of the number (was 17445 now D-017445). My advancing code no longer works and I don't know why! Here is the code: Private Sub GetNextInv_Click() Range("InvNoLast").Value = Range("InvNoLast").Value + 1 End Sub Anyone have an idea on how to fix it? Any help would be most appreciated. TIA -Minitman -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancing A Number With The Push A Button
Private Sub GetNextInv_Click()
Range("InvNoLast").Value = Left(Range("InvNoLast").Value, 2) & _ Format(Right(Range("InvNoLast").Value, _ Len(Range("InvNoLast").Value) - 2) + 1, "000000") End Sub -- Regards, Tom Ogilvy "Minitman" wrote in message ... Greeting, I tried this in the misc group, but I think that was the wrong group, so here goes, again.... I have an invoice that uses a button to advance the invoice number by 1. When I wrote it the numbers were just numbers, but since then a letter has been added to the front of the number (was 17445 now D-017445). My advancing code no longer works and I don't know why! Here is the code: Private Sub GetNextInv_Click() Range("InvNoLast").Value = Range("InvNoLast").Value + 1 End Sub Anyone have an idea on how to fix it? Any help would be most appreciated. TIA -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancing A Number With The Push A Button
Hey Dave,
Thanks for the heads up on the other group, I messed up in that one. -Minitman On Sat, 31 Jul 2004 16:57:34 -0500, Dave Peterson wrote: You got a couple of replies there. Minitman wrote: Greeting, I tried this in the misc group, but I think that was the wrong group, so here goes, again.... I have an invoice that uses a button to advance the invoice number by 1. When I wrote it the numbers were just numbers, but since then a letter has been added to the front of the number (was 17445 now D-017445). My advancing code no longer works and I don't know why! Here is the code: Private Sub GetNextInv_Click() Range("InvNoLast").Value = Range("InvNoLast").Value + 1 End Sub Anyone have an idea on how to fix it? Any help would be most appreciated. TIA -Minitman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancing A Number With The Push A Button
Hey Chip,
That works great. I was wondering, how would I do this with a ScrollBar instead of separate buttons? Is it even possible? I need to go both forward and backward. Thanks for the help. -Minitman On Sat, 31 Jul 2004 16:52:17 -0500, "Chip Pearson" wrote: Try something like Private Sub GetNextInv_Click() Dim S As String Dim N As Long Dim Pos As Integer S = Range("InvNoLast").Value Pos = InStr(1, S, "-") N = CLng(Mid(S, Pos + 1)) N = N + 1 Range("InvNoLast").Value = Left(S, Pos) & Format(N, "000000") End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancing A Number With The Push A Button
Thanks Tom
This looks good, unfortunately, I already went with Chip's solution, but thanks for the reply. -Minitman On Sat, 31 Jul 2004 18:13:45 -0400, "Tom Ogilvy" wrote: Private Sub GetNextInv_Click() Range("InvNoLast").Value = Left(Range("InvNoLast").Value, 2) & _ Format(Right(Range("InvNoLast").Value, _ Len(Range("InvNoLast").Value) - 2) + 1, "000000") End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Push button and input box? | Excel Discussion (Misc queries) | |||
Time with the push of one button | New Users to Excel | |||
push button paste special | Excel Discussion (Misc queries) | |||
Which button did I push | Excel Programming | |||
Searching in a row with push on a button | Excel Programming |