Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
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? --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
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 -- HTH. Best wishes Harald Followup to newsgroup only please. "Zygoid " wrote in message ... 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? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
"Zygoid " wrote in message
... Thanks for your help!! it wasn't exactly what I wanted but it got me in the right direction. Looks neat. Glad you found a solution. Thanks you for the feedback. Best wishes Harald Followup to newsgroup only please. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
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 ... -- HTH. Best wishes Harald Followup to newsgroup only please. "Zygoid " wrote in message ... i have a simular problem again that i cannot figure out. even though the code works great on one cell, how would i get it to work in a range of cells. (...) it works great for cell A1, but I would like A1:A50. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
1) Something is nothing and Nothing is something. Valid tests for object assignments a
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. -- HTH. Best wishes Harald Followup to newsgroup only please. "Zygoid " wrote in message ... Thanks again for a reply! someone had given a suggestion to change the code to this With Target(1) If Union(Target, Range("A1:A50")).Address = "$A$1:$A$50" Then (.....) 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening a form automatically
"Harald Staff" wrote in message
... 4) Please make the "someone" explain the advantages of the suggested change. Ok, you have another thread going, got it. There are usually many ways to get to the same result with VBA. -- HTH. Best wishes Harald Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Close form automatically upon opening workbook | Excel Discussion (Misc queries) | |||
Automate opening an add-in form | Excel Discussion (Misc queries) | |||
Form Numbers Automatically Increment by 1 Upon Opening | New Users to Excel | |||
Opening in Form View | Excel Discussion (Misc queries) | |||
opening a user form | Excel Worksheet Functions |