Clear history from macro VBA Worksheet

android1

Board Regular
Hi,

I have a worksheet I use that lets me add records by clicking an add button. Worksheet uses macros/VBA. Each new entry id is auto incremented by 1.
GG1, GG2 etc

I want to start from scratch so the first entry I add will start from GG1.

Is there a way of resetting the sheet?

I have manually deleted existing entries but it still keeps adding from last entry id.
 

sykes

Well-known Member
You need to post the VBA code that's behind your "Add" button - so that we can see how the incrementation works.
 

android1

Board Regular
You need to post the VBA code that's behind your "Add" button - so that we can see how the incrementation works.
This is the Add code below.

Private Sub CommandButton1_Click()
Dim Owner As String
Dim Coordinator As String
Dim Description As String
Dim RiskType As String
Dim RiskCategory As String
Owner = ComboBox1.Value
Coordinator = ComboBox2.Value
Description = TextBox1.Value
RiskType = ComboBox3.Value
RiskCategory = ComboBox4.Value
If Owner = "" Then
If Coordinator = "" Then
If Description = "" Then
If RiskType = "" Then
If RiskCategory = "" Then
Unload Me
Exit Sub
End If
End If
End If
End If
End If
ActiveSheet.Unprotect Password:="RiskyBusiness"
Dim UniqueAcronym As String
Range("P1").Select
UniqueAcronym = ActiveCell.Value
Dim UniqueIDval As String
UniqueIDval = ActiveCell.Value
Dim Previousnumber As Integer
Sheets("Data Validation").Visible = True
Sheets("Data Validation").Select
Range("K1").Select
Dim Count1 As Integer
Count1 = WorksheetFunction.CountA(Range("K:K"))
If ActiveCell.Value = "" Then
MyID = "1"
ElseIf Count1 = 1 Then
ActiveCell.Offset(1, 0).Range("A1").Select
MyID = "2"
Else
Selection.End(xlDown).Select
Previousnumber = ActiveCell.Value
MyID = Previousnumber + 1
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Dim ID As String
ID = UniqueAcronym & MyID
Sheets("Risk Register").Select
Range("A50000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = ID
Dim MyRow As Variant
MyRow = ActiveCell.Row
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Owner
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Coordinator
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = RiskType
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = RiskCategory
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Description
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Date
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.Value = "=IF(I" & MyRow & "*J" & MyRow & "=0,"""",I" & MyRow & "*J" & MyRow & ")"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.Value = "Open"
ActiveSheet.Protect Password:="RiskyBusiness", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True




Sheets("Risk Action Log").Select
ActiveSheet.Unprotect Password:="RiskyBusiness"
Range("A4").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Dim UniqueIDval2 As String
UniqueIDval2 = ActiveCell.Value
Dim MyRow2 As Variant


If UniqueIDval2 = "" Then
ActiveCell.Value = "='Risk Register'!$A" & MyRow
Else
Range("A50000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = "='Risk Register'!$A" & MyRow
End If
ActiveCell.Offset(0, 1).Range("A1").Select
MyRow2 = ActiveCell.Row


ActiveCell.Value = "=If('Risk Register'!B" & MyRow & "="""","""",'Risk Register'!B" & MyRow & ")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "=If('Risk Register'!C" & MyRow & "="""","""", 'Risk Register'!C" & MyRow & ")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "=if('Risk Register'!F" & MyRow & "="""","""", 'Risk Register'!F" & MyRow & ")"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = "=if('Risk Register'!M" & MyRow & "="""","""", 'Risk Register'!M" & MyRow & ")"
ActiveCell.Offset(0, 5).Range("A1").Select
ActiveCell.Value = "=IF(H" & MyRow2 & "="""","""",IF(I" & MyRow2 & "<>"""",""Complete"",IF(H" & MyRow2 & ">$Q$4,""Not due"",""Overdue"")))"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.Value = "=IF(L" & MyRow2 & "*M" & MyRow2 & "=0,"""",L" & MyRow2 & "*M" & MyRow2 & ")"
ActiveSheet.Protect Password:="RiskyBusiness", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top