autoselect userform

Posted by Dawny on May 31, 2001 2:39 PM

Hi there!

I would like to know if anyone can suggest a way to open a userform based on the value of a cell.

For example if the value of cell A1 begins with "Ford"
then the userform Ford opens, such that if A1 is Ford Capri, the Ford user form opens, is it is Ford Taunus then the Ford form still opens, but if A1 equals BMW 318 the BMW form opens.

thank you guys!!

Dawn

Posted by Russell on May 31, 2001 3:42 PM

I think what you would need to do is to use a Select-Case statement, along with the InStr function. For example:

strMake = Left(Range("A1").Text, InStr(Range("A1").text, " ") - 1 )

Select Case strMake
Case "Ford"
'load the Ford Form
Case "BMW"
'load the BMW form
Case Else
'give error
End Select

The first part would parse out the first word of cell A1 to get the make of the car.

Hope this helps,

Russell

Posted by Dawny on June 01, 2001 2:59 AM

Can't see how to automate

Thanks for the response,

Seems a good way to extract the part of the text that I want to trigger the form, but I need the relavent form to open as soom as data appears in the trigger cell.

THank you!

Dawn

Posted by Dax on June 01, 2001 5:25 AM

Re: Can't see how to automate

Dawn,

If you use the code that Russell gave you but put it into the worksheet code module (right click on the worksheet tab, click View Code).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
strMake = Left(Target.Text, InStr(Target.Text, " ") - 1)

Select Case strMake
Case "Ford"
frmFord.Show
Case "BMW"
frmBMW.Show
Case Else
'give error
End Select

End Sub

HTH,
Dax.