use a txt.value on a form to search a sheet
 

I have this cmdbutton on a form
_______________________________________________
Private Sub CmdAdd_Click()
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open("path to workbook")

ActiveWorkbook.Sheets("1").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txt1.Value
ActiveCell.Offset(0, 1) = txt2.Value
ActiveCell.Offset(0, 2) = txt3.Value

sourcewb.Close True

Unload Me

End Sub
________________________________________________

before closing the sourcewb i would like for the txt3.value to be foun
on sheet(2). if not, then another form needs to open to add txt3 t
sheet(2)

any help would be appreciated

--
Message posted from http://www.ExcelForum.com

use a txt.value on a form to search a sheet
 

Private Sub CmdAdd_Click()
Dim rng2 as Range, rng as Range
Dim SourceWb as Workbook
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open("path to workbook")

With SourceWb.worksheets(1)
set rng2 = .Range("A1")
End With
Do Until isEmpty(rng2)
set rng2 = rng2.offset(1,0)
End If
Loop
rng2.Value = txt1.Value
rng2.Offset(0, 1) = txt2.Value
rng2.Offset(0, 2) = txt3.Value
set rng = SourceWb.Worksheets(2).Cells.Find(txt2.Value)
if rng is nothing then
userform2.show
end if
sourcewb.Close True
Application.ScreenUpdating = True
Unload Me

End Sub

or if the empty cell is always at the bottom:

Private Sub CmdAdd_Click()
Dim rng2 as Range, rng as Range
Dim SourceWb as Workbook
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open("path to workbook")

With SourceWb.worksheets(1)
If isempty(.Range("A1")) then
set rng2 = .Range("A1")
Else
set rng2 = .Range("A65536").End(xlup)(2)
End If
End With
rng2.Value = txt1.Value
rng2.Offset(0, 1) = txt2.Value
rng2.Offset(0, 2) = txt3.Value
set rng = SourceWb.Worksheets(2).Cells.Find(txt2.Value)
if rng is nothing then
userform2.show
end if
sourcewb.Close True
Application.ScreenUpdating = True
Unload Me

End Sub

--
Regards,
Tom Ogilvy



Zygoid wrote in message
...
I have this cmdbutton on a form
_______________________________________________
Private Sub CmdAdd_Click()
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open("path to workbook")

ActiveWorkbook.Sheets("1").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txt1.Value
ActiveCell.Offset(0, 1) = txt2.Value
ActiveCell.Offset(0, 2) = txt3.Value

sourcewb.Close True

Unload Me

End Sub
________________________________________________

before closing the sourcewb i would like for the txt3.value to be found
on sheet(2). if not, then another form needs to open to add txt3 to
sheet(2)

any help would be appreciated.


---
Message posted from http://www.ExcelForum.com/

use a txt.value on a form to search a sheet
 

ahh, works great!

had to create a new form for that form because the form i originally
wanted to open had a syntax to open the workbook that was already open.
ya know the message about "opening a workbook already open".

but it got me into the right direction and i appreciate the help.

my over all goal is to due away with the formulas on the work sheet and
only use VBA. I actually came to this forum looking for formulas,
didn't know a thing about VBA. This forum has been a great source of
info.

Thanks again for your help.


---
Message posted from http://www.ExcelForum.com/