call userform

[rapitorres]

hi. im currently working on a little inventory. little by little things are getting possible thanks for the help of our member here. you know who you are. and Im very Thankful to you guys

here I'am again asking for help. again.


so i have a sheet here. as you can see theres a column name barcode.
it so happen that I'm really using a barcode scanner to scan things and get details from encoded items
and then type in the quantity in the "IN" column or header.

what Id like to happen is every time I scan an item in barcode column, a userform will pop out and gonna ask for the quantity to be enter
and it will pass out to "IN" column

now Ive tried different codes
Private Sub TextBox1_Change()
Range("@Barcode").Value = TextBox1.Value
UserForm1.Show
End Sub

and nothing happens.
hope you can help me with this.

 

[My Aswer Is This]

What would activate the script? How would the script know when you had scanned in a item?
And do you really need a Userform?
Why not just have a Inputbox popup and ask for the quanity.
And how would the script know on what row in column 5 to enter the quanity?

 

[rapitorres]

What would activate the script? How would the script know when you had scanned in a item?
And do you really need a Userform?
Why not just have a Inputbox popup and ask for the quanity.
And how would the script know on what row in column 5 to enter the quanity?

"What would activate the script? How would the script know when you had scanned in a item?"
> everytime i enter numbers on the barcode column

"And do you really need a Userform?
Why not just have a Inputbox popup and ask for the quanity."
> if this is more easy can you teach me or show me?

"And how would the script know on what row in column 5 to enter the quanity?"
> im currently using sumifs function

i have a master list sheet i record every details there

im using this function
=SUMIFS(INTABLE[IN],INTABLE[BARCODE],[@BARCODE])

this is for the out column
=SUMIFS(OUTTABLE[OUT],OUTTABLE[BARCODE],[@BARCODE])

and vlook up in "IN" and "OUT" sheets to

im using table BTW.

hope this makes sense.

 

[My Aswer Is This]

I think you lost me when you started talking about formulas.
So any time you enter any value in column A you want a popup which says How many.
Then if you enter 78 into the popup the 78 will be entered in column 9 of the same row.
Would that do what you want?

 

[rapitorres]

I think you lost me when you started talking about formulas.
So any time you enter any value in column A you want a popup which says How many.
Then if you enter 78 into the popup the 78 will be entered in column 9 of the same row.
Would that do what you want?

absolutely!

 

[My Aswer Is This]

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Now when you enter a value in column A your script will run.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/27/2020  2:21:28 AM  EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = InputBox("How Many")
Target.Offset(, 8).Value = ans
End If
End Sub

 

[rapitorres]

Nice works like a charm!

just tweak some positioning

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 6/27/2020 2:21:28 AM EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = InputBox("Enter Quantity")
Target.Offset(, 4).Value = ans
End If
End Sub

thank you for your help sir. this will ease my job.

 

[rapitorres]

HI. again
I've run to a little problem

when a use a keyboard, type any amount in Col A, it does work
but i tried to use my barcode scanner nothing happens. i know barcode scanning is the same with keystroke so i dont know why.

 

[My Aswer Is This]

Nice works like a charm!

just tweak some positioning

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 6/27/2020 2:21:28 AM EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = InputBox("Enter Quantity")
Target.Offset(, 4).Value = ans
End If
End Sub

View attachment 17065

thank you for your help sir. this will ease my job.

Glad to see you know how to modify script to your needs.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[My Aswer Is This]

HI. again
I've run to a little problem

when a use a keyboard, type any amount in Col A, it does work
but i tried to use my barcode scanner nothing happens. i know barcode scanning is the same with keystroke so i dont know why.

I did wonder if Barcode scanners worked the same as entering values manually. I have never used a Barcode scanner so I have no answer sorry.