Re: Userform to enter entries and also search for and update entries in database
There's nothing wrong with using a Variable multiple times, but you must be sure that the order it is used does not affect later code.
Private Sub userform_initialize() With Me .Caption = "Basecoat Log" 'userform caption .Height = frmHt .Width = frmWidth .ScrollBar1.Min = 2 Set Ws = Worksheets("MasterColour") .ComboColour.List = Ws.Range("ColourList").Value .txtTimestamp.Value = Format(Now, "dd mmmm yyyy hh:mm") .txtBody.SetFocus 'change sheet name and Range here Set Ws = Sheet1 Set MyData = Ws.Range("a2").CurrentRegion 'database .ScrollBar1.Max = MyData.Rows.Count End With End Sub Private Sub cmbDelete_Click() Dim msgResponse As String 'confirm delete Application.ScreenUpdating = False 'get user confirmation msgResponse = MsgBox("This will delete the selected record. Continue?", _ vbCritical + vbYesNo, "Delete Entry") Select Case msgResponse 'action dependent on response Case vbYes 'c has been selected by Find button c.EntireRow.Delete 'remove entry by deleting row Set MyData = Ws.Range("a2").CurrentRegion 'database 'restore form settings With Me .cmbAmend.Enabled = False 'prevent accidental use .cmbDelete.Enabled = False 'prevent accidental use .cmbAdd.Enabled = True 'restore use .ScrollBar1.Max = MyData.Rows.Count 'clear form ClearControls End With Case vbNo Exit Sub 'cancelled End Select Application.ScreenUpdating = True End Sub