msg box for new record

[VBABEGINER]

hi all member,
stuck on msg box. i was tryed but i didnt clicked any better idea.
Require your expert solution.
Msg Box should Apperar with msg when -
a new data added in my col A, say A9.
Msg bOx = "Do you want to add new record?" , vbyesno, "Added Record"
if "Yes"
Record will add in cell A15.
After adding record succesfully,
Msg box "New record Added", vbokonly, msg box exit.
Now, here counter i want to put...say,
if A15 <>"" then
When user try to add new record it should go A16.and going on for every new record.
if "No" msg box exit.
aLL VBA masters and members, request you to have look on this, for ur expert solution.

 

[villy]

Maybe this could give you an idea...

Function AddRecord()
Application.ScreenUpdating = False
Application.EnableEvents = False
With WorksheetFunction.Application
Set myR = Range("B:B")
lrow = Cells(Rows.Count, "B").End(xlUp).Row
'For i = 2 To lrow
Search:
prompt = "Enter New Record"
Title = "New Record"
addrec = InputBox(prompt, Title)
'MsgBox addrec
If addrec = "" Then
'MsgBox "Please enter a Record"
'GoTo Search
Exit Function
Else
For i = 2 To lrow
If Not IsError(.Index(myR, .Match(Val(addrec), myR, 0), 1)) = True Then
'MsgBox "Found"
ans = MsgBox("Record already exist. Do you want to overwrite existing?", vbYesNo, "Record Found")
If ans = vbYes Then
Range("B" & .Match(Val(addrec), myR, 0)).Value = Val(addrec)
updval = InputBox("Enter new value:", "New Value")
Range("C" & .Match(Val(addrec), myR, 0)).Value = Val(updval)
MsgBox "Record: " & "[" & addrec & "]" & " successfully created.", vbInformation + vbOKOnly, "AddNew"
Exit For
Else
GoTo Search
End If
Else
lstrow = lrow + 1
Range("B" & lstrow).Value = Val(addrec)
updval = InputBox("Enter Value:", "New Value")
Range("C" & .Match(Val(addrec), myR, 0)).Value = Val(updval)
sagot = MsgBox("Record: " & "[" & addrec & "]" & _
" successfully added." & vbNewLine & _
"Do you want to add another Record?", vbYesNo, "AddNew")
If sagot = vbYes Then
GoTo Search
Else
Exit For
End If
End If
Next
End If
'Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End With
End Function

 

[VBABEGINER]

yep...great yaar...Villy.
Thanks Dude.

Amazing

 

[villy]

yep...great yaar...Villy.
Thanks Dude.

Amazing

Glad it helps
To God be the glory... WC

 

[villy]

You can also try this based on your requirement..change to suit.

Sub AddRec()
With Range("A9")
    If IsEmpty(Range("A9")) = False Then
    ans = MsgBox("Do you want to add new record?", vbYesNo, "AddNewRecord")
    If ans = vbYes Then
        For x = 9 To Range("A" & Rows.Count).End(xlUp).Row
            If Range("A" & x + 6) = Empty Then
                Range("A" & x + 6).Value = Range("A9")
                Exit Sub
            End If
        Next x
    Else
        Exit Sub
    End If
    End If
End With
End Sub