Results 1 to 3 of 3

Thread: Clear history from macro VBA Worksheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Clear history from macro VBA Worksheet

    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.

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear history from macro VBA Worksheet

    You need to post the VBA code that's behind your "Add" button - so that we can see how the incrementation works.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Feb 2016
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Clear history from macro VBA Worksheet

    Quote Originally Posted by sykes View Post
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •