Clear history from macro VBA Worksheet

android1

Board Regular
Joined
Feb 12, 2016
Messages
62
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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,660
Office Version
365
Platform
Windows
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
Joined
Feb 12, 2016
Messages
62
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,819
Messages
5,483,084
Members
407,379
Latest member
Bender1964

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top