Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Worksheet Through User Form
Hi there!
could any one help me out from a problem in Excel-VBA. I want to update the row of a worksheet through user form. I did it successfully, but in the same workbook. Now the real problem is this I want to update the others workbook's worksheet. Here what I already did . Private Sub cmdAddStudent_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Students") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'check for a Student Name If Trim(Me.txtStudentName.Value) = "" Then Me.txtStudentName.SetFocus MsgBox "Please enter a Student Name" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.txtStudentName.Value ws.Cells(iRow, 2).Value = Me.txtClassId.Value ws.Cells(iRow, 3).Value = Me.txtStudentID.Value ws.Cells(iRow, 4).Value = Me.txtRegNo.Value 'clear the data Me.txtStudentName.Value = "" Me.txtClassId.Value = "" Me.txtStudentID.Value = "" Me.txtRegNo.Value = "" Me.txtStudentName.SetFocus End Sub The code is working fine but i want to update the same record in another worksheet which is present in another workbook on network. The path for that file is \\sharedrive\ShareFile\Public\StuData.xls and Worksheet's name is Students please guys help me out. Saddat Sarfraz Saadi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Worksheet Through User Form
HI, try adding this after you copy to the 1st database:
myFileNameDir = "\\sharedrive\ShareFile\Public\StuData.xls" Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0 Set ws = ActiveSheet.Name 'copy the data to the 2nd database ws.Cells(iRow, 1).Value = Me.txtStudentName.Value ws.Cells(iRow, 2).Value = Me.txtClassId.Value ws.Cells(iRow, 3).Value = Me.txtStudentID.Value ws.Cells(iRow, 4).Value = Me.txtRegNo.Value ActiveWorkbook.Save ActiveWorkbook.Close HTH--Lonnie M. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Worksheet Through User Form
Correction: Not ActiveSheet.Name...
Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0 Set ws = ActiveSheet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Worksheet Through User Form
Thanks Lonnie M, it is wokrking with some little changes. thanks for your
kind help. Here is the complete code for others, that may be help them in their work. this code will update the current Workbook's sheet "Students" and then update the "Students" sheet in the workbook located on share drive. Private Sub cmdAddQuote_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Students") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'check for a Student Name If Trim(Me.txtStudentName.Value) = "" Then Me.txtCompanyName.SetFocus MsgBox "Please enter a Student Name" Exit Sub End If 'copy the data to the 1st database ws.Cells(iRow, 1).Value = Me.txtStudentName.Value ws.Cells(iRow, 2).Value = Me.txtClassId.Value ws.Cells(iRow, 3).Value = Me.txtStudentID.Value ws.Cells(iRow, 4).Value = Me.txtRegNo.Value Dim myFileNameDir As String Dim ws1 As Worksheet Dim iRow1 As Long myFileNameDir = "\\sharedrive\ShareFile\Public\StuData.xls" Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0 Set ws1 = Worksheets("Students") 'find first empty row in database 2 iRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'copy the data to the 2nd database ws1.Cells(iRow1, 1).Value = Me.txtStudentName.Value ws1.Cells(iRow1, 2).Value = Me.txtClassId.Value ws1.Cells(iRow1, 3).Value = Me.txtStudentID.Value ws1.Cells(iRow1, 4).Value = Me.txtRegNo.Value ActiveWorkbook.Save ActiveWorkbook.Close 'clear the data Me.txtStudentName.Value = "" Me.txtClassId.Value = "" Me.txtStudentID.Value = "" Me.txtRegNo.Value = "" Me.txtStudentName.SetFocus End Sub "Lonnie M." wrote: HI, try adding this after you copy to the 1st database: myFileNameDir = "\\sharedrive\ShareFile\Public\StuData.xls" Workbooks.Open Filename:=myFileNameDir, UpdateLinks:=0 Set ws = ActiveSheet.Name 'copy the data to the 2nd database ws.Cells(iRow, 1).Value = Me.txtStudentName.Value ws.Cells(iRow, 2).Value = Me.txtClassId.Value ws.Cells(iRow, 3).Value = Me.txtStudentID.Value ws.Cells(iRow, 4).Value = Me.txtRegNo.Value ActiveWorkbook.Save ActiveWorkbook.Close HTH--Lonnie M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User form or embed in a worksheet | Excel Programming | |||
Modeless User Form and Worksheet Focus | Excel Programming | |||
Pulling information from a worksheet into a user form | Excel Programming | |||
Using user form to change cell value on worksheet | Excel Programming | |||
Edit worksheet while a user form is on? | Excel Programming |