I want to prevent accidental clicking of cmbUpdate, which can ruin the entries previously entered.
Right now, my cmbSave is disabled after I hit the search button but I can't seem to figure out how to disable the Update button until the search button is clicked.
I've tried cmbUpdate.Enabled=False ....and this does not work.
Here is my entire code
Code:
Right now, my cmbSave is disabled after I hit the search button but I can't seem to figure out how to disable the Update button until the search button is clicked.
I've tried cmbUpdate.Enabled=False ....and this does not work.
Here is my entire code
Code:
Code:
Option ExplicitPrivate Sub cmbSave_Click()
Dim iRow As Long
iRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Dim newRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
With ws
iRow = Range("A" & Rows.Count).End(xlUp).Row + 1
'Print batch number to sheet
.Cells(iRow, 1).Value = Me.tbBatch.Value
.Cells(iRow + 1, 1).Value = " "
.Cells(iRow + 2, 1).Value = " "
'Print compounder
.Cells(iRow, 4).Value = Me.TextBox1.Value
.Cells(iRow + 1, 4).Value = Me.TextBox2.Value
.Cells(iRow + 2, 4).Value = Me.TextBox3.Value
'Print downtime issues to sheet
.Cells(iRow, 10).Value = Me.ComboBox1.Value
.Cells(iRow + 1, 10).Value = Me.ComboBox2.Value
.Cells(iRow + 2, 10).Value = Me.ComboBox3.Value
'Print issue completiong date and time to sheet
If Me.CheckBox5.Value = True Then
.Cells(iRow, 12).Value = Time() & Date
End If
If Me.CheckBox6.Value = True Then
.Cells(iRow + 1, 12).Value = Time() & Date
End If
If Me.CheckBox7.Value = True Then
.Cells(iRow + 2, 12).Value = Time() & Date
End If
End With
'Merge product cells
With ws.Cells(iRow, 2)
.Value = Me.tbProduct.Value
.Resize(3).Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'Merge batch completion cell
With ws.Cells(iRow, 7)
If Me.CheckBox3.Value = True Then
ws.Cells(iRow, 7).Value = "YES"
End If
.Resize(3).Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'Print and merge batch start time
With ws.Cells(iRow, 5)
If Me.CheckBox2.Value = True Then
ws.Cells(iRow, 5).Value = Time()
End If
.Resize(3).Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'Print and merge start date
With ws.Cells(iRow, 6)
If Me.CheckBox2.Value = True Then
ws.Cells(iRow, 6).Value = Date
End If
.Resize(3).Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'Print time of downtime issue
With ws.Cells(iRow, 11)
If Me.ComboBox1.Value = "" Then
ws.Cells(iRow, 11) = ""
Else
ws.Cells(iRow, 11) = Time() & Date
End If
End With
'Print time of downtime issue
With ws.Cells(iRow, 11)
If Me.ComboBox2.Value = "" Then
ws.Cells(iRow + 1, 11) = ""
Else
ws.Cells(iRow + 1, 11) = Time() & Date
End If
End With
'Print time of downtime issue
With ws.Cells(iRow, 11)
If Me.ComboBox3.Value = "" Then
ws.Cells(iRow + 2, 11) = ""
Else
ws.Cells(iRow + 2, 11) = Time() & Date
End If
End With
'print and merge end time
With ws.Cells(iRow, 8)
If Me.CheckBox3.Value = True Then
ws.Cells(iRow, 8).Value = Time()
End If
.Resize(3).Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'Print and merge end date
With ws.Cells(iRow, 6)
If Me.CheckBox2.Value = True Then
ws.Cells(iRow, 6).Value = Date
End If
.Resize(3).Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'Print activity categories
With ws
iRow = Range("C" & Rows.Count).End(xlUp).Row + 1
.Cells(iRow, 3).Value = "Release"
.Cells(iRow + 1, 3).Value = "Batching"
.Cells(iRow + 2, 3).Value = "Cleaning"
End With
Unload Me
End Sub
Private Sub cmbCancel_Click()
Unload Me
End Sub
Private Sub cmbSearch_Click()
Dim Nullstring
Application.ScreenUpdating = False
If tbBatch.Value = "" Or Nullstring Then
MsgBox "Please enter a batch number to search."
GoTo error1:
End If
Sheet2.Activate
Dim strFind
Dim rSearch As Range 'range to search
Set rSearch = Sheet2.Range("A1", Range("A65536").End(xlUp))
Dim c
strFind = tbBatch.Value
If strFind = Nullstring Then GoTo error1
With rSearch
Set c = .Find(strFind, LookIn:=xlValues, MatchCase:=True)
If Not c Is Nothing Then 'found it
Select Case MsgBox(strFind & " found", vbOKCancel Or vbExclamation Or vbDefaultButton1)
Case vbOK
Case vbCancel
'do nothing
End Select
c.Select
With Me 'load entry to form
.cmbUpdate.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbSave.Enabled = False 'don't want to duplicate record
.tbProduct.Value = c.Offset(0, 1).Value
.TextBox1.Value = c.Offset(0, 3).Value
.TextBox2.Value = c.Offset(1, 3).Value
.TextBox3.Value = c.Offset(2, 3).Value
.CheckBox3.Value = c.Offset(0, 7).Value
.CheckBox2.Value = c.Offset(0, 5).Value
.ComboBox1.Value = c.Offset(0, 9).Value
.ComboBox2.Value = c.Offset(1, 9).Value
.ComboBox3.Value = c.Offset(2, 9).Value
.CheckBox5.Value = c.Offset(0, 11).Value
.CheckBox6.Value = c.Offset(1, 11).Value
.CheckBox7.Value = c.Offset(2, 11).Value
End With
tbBatch.Locked = False
Else
Sheet2.Activate
MsgBox "No exact match was found. Please try again."
End If
End With
error1:
Sheet2.Activate
End Sub
Private Sub cmbUpdate_Click()
Dim r As Long
Dim c As Range
Dim rng As Range
'ActiveSheet.Unprotect "password"
Application.ScreenUpdating = False
If rng Is Nothing Then GoTo skip
For Each c In rng
If r = 0 Then c.Select
r = r - 1
Next c
skip:
Set c = ActiveCell
c.Value = Me.tbBatch.Value ' write amendments to database
c.Offset(0, 1).Value = Me.tbProduct.Value
c.Offset(0, 3).Value = Me.TextBox1.Value
c.Offset(1, 3).Value = Me.TextBox2.Value
c.Offset(2, 3).Value = Me.TextBox3.Value
c.Offset(0, 9).Value = Me.ComboBox1.Value
c.Offset(1, 9).Value = Me.ComboBox2.Value
c.Offset(2, 9).Value = Me.ComboBox3.Value
c.Offset(0, 7).Value = Me.CheckBox3.Value
If Me.CheckBox3.Value = True Then
If c.Offset(0, 6).Value = "" Then
c.Offset(0, 6).Value = "Yes"
End If
End If
If Me.CheckBox3.Value = True Then
If c.Offset(0, 6) = "" Then
c.Offset(0, 7) = Time()
c.Offset(0, 7).Resize(3).Merge
c.Offset(0, 7).HorizontalAlignment = xlCenter
c.Offset(0, 7).VerticalAlignment = xlCenter
c.Offset(0, 8) = Date
c.Offset(0, 8).Resize(3).Merge
c.Offset(0, 8).HorizontalAlignment = xlCenter
c.Offset(0, 8).VerticalAlignment = xlCenter
End If
End If
If Me.CheckBox5.Value = True Then
If c.Offset(0, 11).Value = "" Then
c.Offset(0, 11).Value = Time() & Date
End If
End If
If Me.CheckBox6.Value = True Then
If c.Offset(1, 11).Value = "" Then
c.Offset(1, 11).Value = Time() & Date
End If
End If
If Me.CheckBox7.Value = True Then
If c.Offset(2, 11).Value = "" Then
c.Offset(2, 11).Value = Time() & Date
End If
End If
If c.Offset(0, 9).Value = Me.ComboBox1.Value Then
If c.Offset(0, 10).Value = "" Then
c.Offset(0, 10).Value = Time() & Date
End If
End If
If c.Offset(1, 9).Value = Me.ComboBox2 Then
If c.Offset(1, 10).Value = "" Then
c.Offset(1, 10).Value = Time() & Date
End If
End If
If c.Offset(2, 9).Value = Me.ComboBox3.Value Then
If c.Offset(2, 10).Value = "" Then
c.Offset(2, 10).Value = Time() & Date
End If
End If
'restore Form
With Me
.cmbUpdate.Enabled = False
.cmbSave.Enabled = True
End With
If Sheet2.AutoFilterMode Then Sheet2.Range("A7").AutoFilter
Application.ScreenUpdating = True
On Error GoTo 0
'ActiveSheet.Protect "password", True, True
Unload Me
End Sub
Private Sub ComboBox1_DropButtonClick()
Me.ComboBox1.AddItem "Kettle"
Me.ComboBox1.AddItem "Turbo Shear"
Me.ComboBox1.AddItem "Waiting Material"
Me.ComboBox1.AddItem "Waiting Documentation"
Me.ComboBox1.AddItem "Waiting Mechanic"
Me.ComboBox1.AddItem "Waiting Compounder"
End Sub
Private Sub ComboBox2_DropButtonClick()
Me.ComboBox2.AddItem "Kettle"
Me.ComboBox2.AddItem "Turbo Shear"
Me.ComboBox2.AddItem "Waiting Material"
Me.ComboBox2.AddItem "Waiting Documentation"
Me.ComboBox2.AddItem "Waiting Mechanic"
Me.ComboBox2.AddItem "Waiting Compounder"
End Sub
Private Sub ComboBox3_DropButtonClick()
Me.ComboBox3.AddItem "Kettle"
Me.ComboBox3.AddItem "Turbo Shear"
Me.ComboBox3.AddItem "Waiting Material"
Me.ComboBox3.AddItem "Waiting Documentation"
Me.ComboBox3.AddItem "Waiting Mechanic"
Me.ComboBox3.AddItem "Waiting Compounder"
End Sub
Private Sub UserForm_Click()
End Sub
Last edited by a moderator: