I tried with this code. But it is not stopping the entry of the duplicate
row with the same file number.
file numbers may be given consequently 1,2,3 .....,
if we enter file number 1 for twice, it would fire msgbox that it already
exists.
pls provide solution
On Wed, Sep 7, 2011 at 8:03 PM, mohd saber <md.saber2...@gmail.com> wrote:
> Hi Shankar,
>
> Please find the below updated data for the command file
>
> Private Sub CommandButton1_Click()
> ActiveWorkbook.Sheets("Employee Details").Activate
> Range("A1").Select
> Do
>
> If IsEmpty(ActiveCell) = False Then
> If activecell.value = txtfileno.value Then
> ActiveCell.Offset(0, 1) = txtname.Text
> EndIf
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until IsEmpty(ActiveCell) = True
> ActiveCell.Value = txtfileno.Value
> ActiveCell.Offset(0, 1) = txtname.Text
> Regards,
> saber
> On Wed, Sep 7, 2011 at 6:58 PM, Bheema Shankar <shankar.n...@gmail.com>wrote:
>
>> I placed the following objects on my VBA excel
>>
>> TextBoxes
>> a) file no
>> b) Name
>>
>> Command buttons
>> a) Save
>> b) Update
>>
>> @@@@@@@@@@@@In save button I have given code as@@@@@@@@@@@@@@@
>>
>> Private Sub CommandButton1_Click()
>> ActiveWorkbook.Sheets("Employee Details").Activate
>> Range("A1").Select
>> Do
>> If IsEmpty(ActiveCell) = False Then
>> ActiveCell.Offset(1, 0).Select
>> End If
>>
>> Loop Until IsEmpty(ActiveCell) = True
>> ActiveCell.Value = txtfileno.Value
>> ActiveCell.Offset(0, 1) = txtname.Text
>>
>> @@@@@@@@@@@@In Update button i have given the code as @@@@@@@@@@@
>>
>> Dim ws As Worksheet
>> Dim uf As UserForm
>> Dim wslastrw As Long, filerow As Long
>> Dim fileno As String
>> Dim filefnd As Range
>>
>> Set uf = formcalsheet
>>
>> fileno = uf.txtfileno.Text
>>
>> If fileno = "" Then
>> MsgBox "Please enter a valid File No."
>> Exit Sub
>> End If
>>
>> Set ws = Worksheets("Employee Details")
>> wslastrw = ws.Range("A" & Rows.Count).End(xlUp).Row
>>
>> With ws
>>
>> Set filefnd = .Range("A2:A" & wslastrw).Find(what:=fileno,
>> after:=.Range("A2"), _
>> LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows,
>> searchdirection:=xlNext, _
>> MatchCase:=False)
>> If filefnd Is Nothing Then
>> MsgBox "No File with this File No. exists." & Chr(10) &
>> "Please enter a valid File No."
>> Exit Sub
>> Else
>> filerow = filefnd.Row
>> End If
>>
>> uf.txtname.Value = ws.Range("B" & filerow).Value
>>
>>
>>
>> PROBLEM IS#############
>>
>> while clicking the update button after giving the file number the form
>> will present the corresponding data to that particular number
>>
>> If I modify any change and clicks the save button again a fresh row is
>> creating with the same number
>>
>>
>> It should be avoided. The data should be amended in the existing row
>> itself. whats wrong with the code. provide me solution
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Regards,
> Saber
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
