Auto filling bill number and date
Solved/Closed
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
-
Mar 17, 2014 at 07:37 PM
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014 - May 7, 2014 at 10:58 AM
jowelvjoy Posts 9 Registration date Monday March 17, 2014 Status Member Last seen May 7, 2014 - May 7, 2014 at 10:58 AM
Related:
- Auto filling bill number and date
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Facebook auto refresh - Guide
- Grand theft auto iv download apk for pc - Download - Action and adventure
- Nvidia drivers auto detect - Guide
- Auto redial samsung - Guide
16 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 19, 2014 at 01:10 AM
Mar 19, 2014 at 01:10 AM
download the workbook form this web page
http://speedy.sh/cGF3s/JOWEL-30319-acc.xlsm
RIGHT CLICK SHEET "INPUT" and click view code
you can see the event code
(alternately open vbeditor (a't+F11) you will see the code. at top you can see that this is code for sheet1 because input is the first sheet.
you need not do anything (once you fill up the data in sheet "input"0 except you have to DOUBLE CLICK G6 IN INPUT SHEET.
http://speedy.sh/cGF3s/JOWEL-30319-acc.xlsm
RIGHT CLICK SHEET "INPUT" and click view code
you can see the event code
(alternately open vbeditor (a't+F11) you will see the code. at top you can see that this is code for sheet1 because input is the first sheet.
you need not do anything (once you fill up the data in sheet "input"0 except you have to DOUBLE CLICK G6 IN INPUT SHEET.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 20, 2014 at 02:20 AM
Mar 20, 2014 at 02:20 AM
not clear to me.
do u want input sheet to be last sheet instead of firsts sheet
I suppose that does not make any change. if wherever input sheet is right click that tab and copy the code
in this present file wherever you shift the input sheet by clicking the tab and dragging to second place the code will be there (check this by right clicking tab from the new location and clinking view code)
make another expt open vb editor and hit control+R the sheet will continue to be called as sheet1.
do u want input sheet to be last sheet instead of firsts sheet
I suppose that does not make any change. if wherever input sheet is right click that tab and copy the code
in this present file wherever you shift the input sheet by clicking the tab and dragging to second place the code will be there (check this by right clicking tab from the new location and clinking view code)
make another expt open vb editor and hit control+R the sheet will continue to be called as sheet1.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 18, 2014 at 03:15 AM
Mar 18, 2014 at 03:15 AM
sheet is like this from A1
NAME Narayanan
CUSTOMER NUMBER 17 doublecllick B2
DATE 7/3/2014
BILL NUMBER 147
sheet 2 is like this
cust nr name
1 Noorudeen
2 Brees Willa
3 Shereefa
4 JAMEEL
5 Muhammad Fisal
6 Hasinar
7 Fathibi
8 Saibunneesa
9 Jabbar
10 NEW
11 Ramakrishnan
12 Sawdamini Teacher
13 Remeshan Mash Near saudamini teacher
14 NEW
15 NEW
16 Vimod Kumar
17 Narayanan
right click TAB of sheet 2 and click view code
in the window that comes up copy this event code
SAVE THE FILE AS MACRO ENABLED FILE
NOW DOUBLE CLICK SHEET1 B2 REPEAT SHEET1 B2
SEE WHAT HAPPENS.
remember when you make entries do not add unnecessary spaces either. for e.g. in sheet narayanan has a space at the end. The name and other data in sheet 1 and sheet2 must be the same
NAME Narayanan
CUSTOMER NUMBER 17 doublecllick B2
DATE 7/3/2014
BILL NUMBER 147
sheet 2 is like this
cust nr name
1 Noorudeen
2 Brees Willa
3 Shereefa
4 JAMEEL
5 Muhammad Fisal
6 Hasinar
7 Fathibi
8 Saibunneesa
9 Jabbar
10 NEW
11 Ramakrishnan
12 Sawdamini Teacher
13 Remeshan Mash Near saudamini teacher
14 NEW
15 NEW
16 Vimod Kumar
17 Narayanan
right click TAB of sheet 2 and click view code
in the window that comes up copy this event code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim nname As String, ddate As Date, billnr As Long, custnr As Long, cfind As Range
Cancel = True
If Target.Address <> "$B$2" Then GoTo enableevents
If Target = "" Then GoTo enableevents
On Error GoTo enableevents
Application.enableevents = False
With Worksheets("sheet1")
nname = .Range("B1")
custnr = Target
ddate = .Range("B3")
billnr = .Range("B4")
End With
With Worksheets("sheet2")
Set cfind = .Columns("A:A").Find(what:=custnr, lookat:=xlWhole)
If Not cfind Is Nothing Then
If Trim(cfind.Offset(0, 1)) = nname Then
cfind.Offset(0, 2) = Format(ddate, "m/d/yy")
cfind.Offset(0, 3) = billnr
End If
End If
.Activate
End With
MsgBox "macro done. check sheet2"
enableevents:
Application.enableevents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
SAVE THE FILE AS MACRO ENABLED FILE
NOW DOUBLE CLICK SHEET1 B2 REPEAT SHEET1 B2
SEE WHAT HAPPENS.
remember when you make entries do not add unnecessary spaces either. for e.g. in sheet narayanan has a space at the end. The name and other data in sheet 1 and sheet2 must be the same
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
Mar 18, 2014 at 11:26 PM
Mar 18, 2014 at 11:26 PM
https://www.dropbox.com/s/3o1b6xvpkqo4prf/acc.xlsx
this is my work book please can you check this
this is my work book please can you check this
Didn't find the answer you are looking for?
Ask a question
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
Mar 19, 2014 at 01:16 PM
Mar 19, 2014 at 01:16 PM
now everything working perfectly but i need a small help
instead of double click in g6
after entering all data like date and bill number
normally we will hit the enter button then the code should run and courser should go to g6
instead of double click in g6
after entering all data like date and bill number
normally we will hit the enter button then the code should run and courser should go to g6
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 20, 2014 at 01:23 AM
Mar 20, 2014 at 01:23 AM
As far as I know (I am not an expert) it is not possible to write a macro (even if this macro may be assigned to the button) to fire a doubleclick on a cell. I wonder what is the great difference in clicking a button or double clicking a cell.
I do not want to use "selection_change" or "change" event code because then there is a complication if your first enter G6 then that event code will be immediately fired giving a wrong answer. This means you have to be extremely careful to enter G6 only last after filling G7 and G8. This will be extremely difficult unless you are very careful
Better accept double click G6 in which case in any order you can fill up data in G6,G7 and G8.
did you try double digit number in G6 like 15 or 16 did it work if not revert back to newsgroup
I do not want to use "selection_change" or "change" event code because then there is a complication if your first enter G6 then that event code will be immediately fired giving a wrong answer. This means you have to be extremely careful to enter G6 only last after filling G7 and G8. This will be extremely difficult unless you are very careful
Better accept double click G6 in which case in any order you can fill up data in G6,G7 and G8.
did you try double digit number in G6 like 15 or 16 did it work if not revert back to newsgroup
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
Mar 20, 2014 at 02:13 AM
Mar 20, 2014 at 02:13 AM
Ok then can we make a small change end with instated of base input sheat as the last sheet
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
Mar 20, 2014 at 02:35 AM
Mar 20, 2014 at 02:35 AM
input sheet to be last sheet instead of last sheet then next entry will be easy
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 20, 2014 at 03:45 AM
Mar 20, 2014 at 03:45 AM
"LAST sheet to LAST sheet. "
some spelling mistake
putting input sheet as last sheet does not solve the problem
If I use "selection_change" or "change" event code this is not the problem
in INPUT SHEET
the cells G7 and G8 must be filled first and only G6 last.
even here as soon as you select G6 to fill the customer number, the event code will be fired giving some probem or bug in the case of selection_change event
but "change" event is fired only after you enter something in G6 and then hit enter key. You must study some more on the EVENT CODES.
is it possible for you or your team member or user to be so careful????????
some spelling mistake
putting input sheet as last sheet does not solve the problem
If I use "selection_change" or "change" event code this is not the problem
in INPUT SHEET
the cells G7 and G8 must be filled first and only G6 last.
even here as soon as you select G6 to fill the customer number, the event code will be fired giving some probem or bug in the case of selection_change event
but "change" event is fired only after you enter something in G6 and then hit enter key. You must study some more on the EVENT CODES.
is it possible for you or your team member or user to be so careful????????
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
Mar 22, 2014 at 01:43 AM
Mar 22, 2014 at 01:43 AM
https://www.dropbox.com/s/iohw4dmqhxstkoq/jowel111.xlsm
I TRIED TO UPDATE THE MACRO BUT UNFORTUNATELY IT IS NOT WORKING CAN YOU EDIT THIS THING
I TRIED TO UPDATE THE MACRO BUT UNFORTUNATELY IT IS NOT WORKING CAN YOU EDIT THIS THING
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 22, 2014 at 04:12 AM
Mar 22, 2014 at 04:12 AM
you did small two slips
formerly there were two sheets now only one sheet that the name is not input but sheet1
see modified event code below
formerly there were two sheets now only one sheet that the name is not input but sheet1
see modified event code below
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim nname As String, ddate As Date, billnr As Long, custnr As Long, cfind As Range
Cancel = True
If Target.Address <> "$D$1" Then GoTo enableevents
If Target = "" Then GoTo enableevents
On Error GoTo enableevents
Application.enableevents = False
With Worksheets("sheet1")
nname = .Range("F1")
custnr = "00" & Target
ddate = .Range("D2")
billnr = .Range("D3")
'End With
'With Worksheets("sheet1")
Set cfind = .Columns("G:G").Find(what:=custnr, lookat:=xlWhole)
If Not cfind Is Nothing Then
If Trim(cfind.Offset(0, 1)) = nname Then
cfind.Offset(0, 2) = Format(ddate, "m/d/yy")
cfind.Offset(0, 3) = billnr
End If
End If
.Activate
End With
MsgBox "macro done. check sheet1 columns I and J"
enableevents:
Application.enableevents = True
End Sub
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
Mar 22, 2014 at 04:46 AM
Mar 22, 2014 at 04:46 AM
THANKS THANKS
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 22, 2014 at 06:33 AM
Mar 22, 2014 at 06:33 AM
welcome
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
Mar 25, 2014 at 03:42 AM
Mar 25, 2014 at 03:42 AM
https://www.dropbox.com/s/ca7i7n9zj4c4315/test.xlsm
can you give me some help please
before pasting the data it should check the box A3 and paste the value to correspondent row
example A3 have 1/14 then data shuld paste in I and J
if A3 have 2/14 then data shuld paste in K and L
can you give me some help please
before pasting the data it should check the box A3 and paste the value to correspondent row
example A3 have 1/14 then data shuld paste in I and J
if A3 have 2/14 then data shuld paste in K and L
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 25, 2014 at 09:47 AM
Mar 25, 2014 at 09:47 AM
event code modified
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim nname As String, amount As Long, billnr As Long, custnr As Long, cfind As Range, cfind1 As Range
Dim r2 As Range
Cancel = True
If Target.Address <> "$D$1" Then GoTo enableevents
If Target = "" Then GoTo enableevents
On Error GoTo enableevents
Application.enableevents = False
With Worksheets("sheet1")
Set r2 = Range(.Range("I1"), .Cells(1, Columns.Count).End(xlToLeft))
Set cfind1 = r2.Find(what:=Range("A3"), lookat:=xlWhole)
nname = .Range("F1")
custnr = "00" & Target
amount = .Range("D2")
billnr = .Range("D3")
End With
With Worksheets("sheet1")
Set cfind = .Columns("G:G").Find(what:=custnr, lookat:=xlWhole)
If Not cfind Is Nothing Then
If Trim(cfind.Offset(0, 1)) = nname Then
.Cells(cfind.Row, cfind1.Column) = amount
.Cells(cfind.Row, cfind1.Column + 1) = billnr
'cfind.Offset(0, 2) = amount
'cfind.Offset(0, 3) = billnr
End If
End If
.Activate
End With
enableevents:
Application.enableevents = True
End Sub
jowelvjoy
Posts
9
Registration date
Monday March 17, 2014
Status
Member
Last seen
May 7, 2014
May 7, 2014 at 10:58 AM
May 7, 2014 at 10:58 AM
hi sir
this is my account ledger
https://www.dropbox.com/s/5yxmj8zt3oksjv7/final.xlsm
in this if i pressed the save button in SHEET 4 ( EDIT CUST )
check j32 and
j33 to in sheet 1 (input) need to check the corespondent serial number and past to U
j34 THIS IN Q
j35 THIS IN R
j36 THIS IN S
j37 to in sheet 11 (inp) v
j38 this in w
j39 this in x
will you help me to make this code please
this is my account ledger
https://www.dropbox.com/s/5yxmj8zt3oksjv7/final.xlsm
in this if i pressed the save button in SHEET 4 ( EDIT CUST )
check j32 and
j33 to in sheet 1 (input) need to check the corespondent serial number and past to U
j34 THIS IN Q
j35 THIS IN R
j36 THIS IN S
j37 to in sheet 11 (inp) v
j38 this in w
j39 this in x
will you help me to make this code please