opening a form automatically

  • Thread starter Thread starter Zygoid
  • Start date Start date
Z

Zygoid

I have created a form (using a cmd button on the worksheet, VBA) for
adding new customers to a different workbook. is there a way to have
this form open automatically if a customer name (text) that is typed
into a cell is not listed in a specified table in the other workbook?
 
Hi

One way among several:
With the name list in Sheet number 2 cells A1 and downwards, and for name entry in another
sheet's A1; rightclick the sheet tab of the entry sheet, choose "View code", paste this
in:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Address = "$A$1" Then
If .Value <> "" Then
If Sheets(2).Range("A1:A200").Find( _
What:=.Value) Is Nothing Then
UserForm1.Show 'rename form to fit
End If
End If
End If
End With
End Sub
 
Thanks for your help!!

it wasn't exactly what I wanted but it got me in the right direction.
This is what I ended up with;

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Address = "$D$3" Then
If .Value <> "" Then
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("?") '? = path to workbook
ActiveWorkbook.Sheets("??").Activate '?? = sheet name
If Sheets("??").Range("A1:A200").Find( _
What:=.Value) Is Nothing Then
SourceWB.Close True
frmNewCust.Show
End If
End If
End If
End With
End Sub


Thanks again for your help, couldn't have figured it out without you
help
 
Zygoid > said:
Thanks for your help!!

it wasn't exactly what I wanted but it got me in the right direction.
Click to expand...

Looks neat. Glad you found a solution. Thanks you for the feedback.

Best wishes Harald
Followup to newsgroup only please.
 
i have a simular problem again that i cannot figure out. even though th
code works great on one cell, how would i get it to work in a range o
cells.

example;

With Target(1)
If .Address = "$A$1" Then
If .Value <> "" Then
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("path\to\workbook")
ActiveWorkbook.Sheets("1").Activate
If Sheets("1").Range("A1:A50").Find( _
What:=.Value) Is Nothing Then
SourceWB.Close True
FORM.Show
End If
End If
End If
End With

it works great for cell A1, but I would like A1:A50. I have trie
changing the .address to "$A$1:$A$50" but no go.
I've tried many combonations, but none wor
 
That's obvious <g>.. no, it's not, but this should work:

If Not Intersect(Target(1), Range("A1:A50")) Is Nothing Then

"Intersect" is the area the two ranges have in common. So if your cell has a cell or more
in common with range A1:A50 then ...
 
Thanks again for a reply! someone had given a suggestion to change th
code to this

With Target(1)
If Union(Target, Range("A1:A50")).Address = "$A$1:$A$50" Then
If .Value <> "" Then
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("path\to\workbook")
ActiveWorkbook.Sheets("1").Activate
If Sheets("1").Range("A1:A50").Find( _
What:=.Value) Is Nothing Then
SourceWB.Close True
FORM.Show 'rename form to fit
End If
End If
End If
End With

This works great to open the form if text in cell is not listed, but
have discovered another problem..

if the text in the cell is listed, the sourceWB will not close. i
stays open and is activated.

i figured i would need to add something like

If Sheets("1").Range("A1:A50").Find( _
What:=.Value) Is Something Then
SourceWB.Close True
end if

I have tried many variations by changing the word "nothing"
but it doesn't work. Any suggestions
 
1) Something is nothing and Nothing is something. Valid tests for object assignments are:

If Whatever Is Nothing then

and opposite

If Not Whatever Is Nothing then

2) You are opening a file for every entry in A1:A50. Seems pretty slow. Consider another
design.

3) You say what will happen when something is rue, but not what should happen if not.
Instead of

If Dangerous then
Get Out
End if

rewrite to

If Dangerous then
Get Out
Else
Stay
Order Another
End if

4) Please make the "someone" explain the advantages of the suggested change.
 
Harald Staff said:
4) Please make the "someone" explain the advantages of the suggested change.
Click to expand...

Ok, you have another thread going, got it.
There are usually many ways to get to the same result with VBA.
 
Back
Top