Self deleting cell formulas

**dangelor** · 02-12-2012, 03:11 PM

To utilize the code, right click the name tab on the Master_Database worksheet. Select View Code. Paste the code into the window that opens. Every time something on the worksheet changes, such as adding a line of data, the code will execute.

**SJJ** · 02-12-2012, 03:54 PM

Originally Posted by dangelor

To utilize the code, right click the name tab on the Master_Database worksheet. Select View Code. Paste the code into the window that opens. Every time something on the worksheet changes, such as adding a line of data, the code will execute.

I've sort of got it to work now (it's filling in the right column but doesn't recognize that the database headings are on row 50 and the data fills in from row 51, then 52, 53 etc.) but I'll explain why I won't be able to use it:

The background to the issue I had using the spreadsheet formula as shown in my first post was that if an entry in the database was deleted, all the Number IDs would reassign themselves. So where you had 001, 002, 003, 004, 005 for example, if you deleted database row 004, the numbers would change to 001, 002, 003, 004 instead of: 001, 002, 003, 005. This issue could be solved by getting VBA code to assign the number but that's where I got stuck. See my post before last for the full layout of the spreadsheet / database I have.

Thanks for the suggestion though, I feel I'm getting closer to the solution!

**dangelor** · 02-12-2012, 04:02 PM

Can you post a dummy file?

**OnErrorGoto0** · 02-12-2012, 04:08 PM

If your form is writing to the database file, why not have it simply overwrite the formula with its value after entering the new data?

**royUK** · 02-13-2012, 02:23 AM

I suggested how to do this, gave you some leads not completed code as you asked. You haven't even used the suggestions.

You have a userform that adds data to the sheet so you don't need sheet formulas for this.

If the unique number is in Column A then

Option Explicit

Private Sub CommandButton1_Click()
    Dim NextRw As Long
    With Sheet1    '< sheet that has database
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = Format(Application.WorksheetFunction.Max(.Columns(1)) + 1, "000")
        'add rest of data
    End With
End Sub

**SJJ** · 02-13-2012, 03:08 AM

Originally Posted by royUK

I suggested how to do this, gave you some leads not completed code as you asked. You haven't even used the suggestions.

You have a userform that adds data to the sheet so you don't need sheet formulas for this.

If the unique number is in Column A then

Option Explicit

Private Sub CommandButton1_Click()
    Dim NextRw As Long
    With Sheet1    '< sheet that has database
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = Format(Application.WorksheetFunction.Max(.Columns(1)) + 1, "000")
        'add rest of data
    End With
End Sub

Thank you for your help. I did post an attempt with your initial suggestion (Post #12) but as I am still developing a basic understanding of VBA, I spend a lot of time not getting very far. I don't post all these attempts as I was didn't want to fill the board up with code that might divert from my original issue.

I will have a go with the above.

**SJJ** · 02-13-2012, 08:29 AM

Originally Posted by royUK

I suggested how to do this, gave you some leads not completed code as you asked. You haven't even used the suggestions.

You have a userform that adds data to the sheet so you don't need sheet formulas for this.

If the unique number is in Column A then

Option Explicit

Private Sub CommandButton1_Click()
    Dim NextRw As Long
    With Sheet1    '< sheet that has database
        NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(NextRw, 1).Value = Format(Application.WorksheetFunction.Max(.Columns(1)) + 1, "000")
        'add rest of data
    End With
End Sub

Brilliant! It works. I've incorperated this suggestion into what I had and ended up with the below:

 Private Sub Submit_Click()
If MsgBox("Send to Database?", vbYesNo + vbQuestion) = vbNo Then
   Exit Sub
      
      Else
          
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master_Database")
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

ws.Cells(iRow, 2).Value = Application.WorksheetFunction.Max(Range("B50:B1050")) + 1  '< I set the first number of the range (cell B51, which is an "example" line in the database) as "000"

'This transfers values from userform to database sheet.
ws.Cells(iRow, 3).Value = Me.Answer1.Value
ws.Cells(iRow, 4).Value = Me.Answer2.Value
ws.Cells(iRow, 5).Value = Me.Answer3.Value

'This clears the userform
Me.Answer1.Value = ""
Me.Answer2.Value = ""
Me.Answer3.Value = ""
Me.Answer1.SetFocus

End If
End Sub

Sorry it took so long for all this to click in my head. I realise how simple the solution is now - particularly as I essentially had all the more complicated code I needed already. Thank you for your patience and for everybody's help. Much appreciated - I have learnt a lot in this short time!