<ユーザーフォームのコード>
Private Sub CommandButton1_Click()
Application.Visible = True
UserForm1.Hide
Worksheets("INVENTORY").Activate
End Sub
------------------------------------------------------
Private Sub CommandButton3_Click()
Call FormStart2
End Sub
※モジュールのパブリックプロシージャで、テキストボックス1にバーコード値
その後、右のボタンを押して、ボックス10までの値を貼り付けさせます。
-------------------------------------------------------
Private Sub TextBox7_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With Worksheets("INVENTORY")
TextBox9.Value = .Cells(RowNr, 10).Value
TextBox10.Value = .Cells(RowNr, 10).Value + TextBox7.Value
End With
End Sub
------------------------------------------------------
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "Please use [Form Close] button"
Cancel = True
End If
End Sub
------------------------------------------------------
Private Sub CommandButton2_Click()
Dim rc As Integer
rc = MsgBox("Is In-Out Qty correct?", vbYesNo + vbExclamation, "Final Check")
If rc = vbYes Then
MsgBox "DATABASE UPDATE"
Else
MsgBox "UPDATE CANCEL"
End If
With ListBox1
If .ListIndex = -1 Then
MsgBox "LISTBOX NOT SELECTED"
GoTo SkipAction
Else
Worksheets("INVENTORY").Cells(RowNr, 10).Value = .List(.ListIndex, 0)
End If
End With
With Worksheets("INVENTORY")
.Cells(RowNr, 2).Value = TextBox2.Value
.Cells(RowNr, 2).Value = TextBox2.Value
.Cells(RowNr, 7).Value = TextBox7.Value
.Cells(RowNr, 9).Value = TextBox9.Value
.Cells(RowNr, 10).Value = TextBox10.Value
End With
End Sub
※バーコード読取値を出力させるシートは、非表示です。
-------------------------------------------------
<モジュール1のコード>
Public RowNr As Long
Public Rng As Range
Public InputValue As String
Option Explicit
※パブリック変数・プロシージャを使いました(ユーザーフォームの操作)
-------------------------------------------------
Sub FormStart()
Load UserForm1
UserForm1.Show
UserForm1.TextBox1.SetFocus
End Sub
------------------------------------------------
Sub TextBox1_Change()
Worksheets("BARCODE").Activate
If ActiveCell.Address = "$A$2" Then
InputValue = Worksheets("BARCODE").Cells(1, 1).Value
InputValue = UserForm1.TextBox1.Value
Else
GoTo SkipAction
End If
End Sub
--------------------------------------------------
Sub FormStart2()
Worksheets("INVENTORY").Activate
Set Rng = Range("A3:A100000").Find(What:=UserForm1.TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Rng Is Nothing Then
MsgBox "NOT FOUND"
UserForm1.TextBox1.Value = ""
UserForm1.TextBox1.SetFocus
GoTo SkipAction
End If
RowNr = Rng.Row
With UserForm1.ListBox1
.AddItem "SUPPLY"
.AddItem "PRODUCTION"
.AddItem "SERVICE"
End With