Selecting different forms from Drop Down option

[I heart Excel]

Hello,

I am looking to have a front form, whereby the user can select either Sickness or Annual Leave from a dropdown.

Once one is selected the necessary form will appear. I have had a look online and I think it can be done. Or maybe a way in which you have both forms under one another and depending on the option you select one of the forms blacks out.

I tried doing VLookup but not sure this is the right path to go down.

Thanks

 

[StillLearningExcel]

Hello,

I am looking to have a front form, whereby the user can select either Sickness or Annual Leave from a dropdown.

Once one is selected the necessary form will appear. I have had a look online and I think it can be done. Or maybe a way in which you have both forms under one another and depending on the option you select one of the forms blacks out.

I tried doing VLookup but not sure this is the right path to go down.

Thanks

Hi,

If you can put one form on one tab and the other on another, put a dropdown on your front sheet.

Enter the following in VBA agaist your front sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If [a4] = "Sickness" Then
Sheets("Sickness").Select
End If

If [a4] = "Holiday" Then
Sheets("Holiday").Select
End If
End Sub


You'll need to name your sheets as above (Holiday & Sickness or change the script and also a4 is where is put the dropdown you can change this buta again you'll need to change it in the script.

Hope this helps!!

 

[bertie]

If you are using a UserForm then rather than having several forms I would use a MultiPage control.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   [COLOR=darkblue]With[/COLOR] ComboBox1
      .AddItem "Sickness"
      .AddItem "Annual Leave"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ComboBox1_Change()
   MultiPage1.Value = ComboBox1.ListIndex + 1
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[I heart Excel]

Hello,

thanks that helped and worked!

Another question, which I have been hunting and testing for, but haven't come across anything.

Again within the same sheet, for example the Sickness Form, I select Yes or No from a drop down option.

Yes - would open bring up additional fields to fill out
No - wouldn't open the additional fields.

I have had a look in the Scripting and would it be to do with the Set Data or Change option?

Thanks for your help

 

[I heart Excel]

Bertie - I haven't tried this as I haven't used UserForms before, am having a look now, Thanks

 

[StillLearningExcel]

Hello,

thanks that helped and worked!

Another question, which I have been hunting and testing for, but haven't come across anything.

Again within the same sheet, for example the Sickness Form, I select Yes or No from a drop down option.

Yes - would open bring up additional fields to fill out
No - wouldn't open the additional fields.

I have had a look in the Scripting and would it be to do with the Set Data or Change option?

Thanks for your help

You could try something like this, when no is selected it will hide the rows and yes will open them again.

Private Sub Worksheet_Change(ByVal Target As Range)
If [a4] = "yes" Then
Rows("11:35").Select
Selection.EntireRow.Hidden = False
End If

If [a4] = "no" Then
Rows("11:35").Select
Selection.EntireRow.Hidden = True
End If
End Sub

There might be a better way of doing this but I'm new to VBA too

 

[I heart Excel]

Is there any particular reason why I can't put this formula in twice for one spreadsheet.

I currently have -

Private Sub Worksheet_Change(ByVal Target As Range)
If [E18] = "Other - Please Specify Below" Then
Rows("19:21").Select
Selection.EntireRow.Hidden = False
End If
If [E18] = "Daily" Then
Rows("19:21").Select
Selection.EntireRow.Hidden = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If [e25] = "yes" Then
Rows("29:30").Select
Selection.EntireRow.Hidden = False
End If
If [e25] = "no" Then
Rows("29:30").Select
Selection.EntireRow.Hidden = True
End If
End Sub

I am getting the following error -
Compile Error:
Ambiguous name detected: Worksheet_Change

Any thoughts? Thanks

 

[bertie]

The Worksheet_Change event triggers when changes are made to the worksheet. You can only have one Worksheet_Change event per worksheet module.

You are getting the, "ambiguous", warning because you have two procedures with the same name: Private Sub Worksheet_Change

You can use the Target.Address property to identify which cell was changed. See below:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
 
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Target.Address
      [COLOR=darkblue]Case[/COLOR] "$E$18"
         [COLOR=darkblue]If[/COLOR] UCase(Target.Value) = "YES" [COLOR=darkblue]Then[/COLOR]
            Rows("29:30").EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
         [COLOR=darkblue]ElseIf[/COLOR] UCase(Target.Value) = "NO" [COLOR=darkblue]Then[/COLOR]
            Rows("29:30").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Case[/COLOR] "$E$25"
         [COLOR=darkblue]If[/COLOR] UCase(Target.Value) = "DAILY" [COLOR=darkblue]Then[/COLOR]
            Rows("19:21").EntireRow.Hidden = [COLOR=darkblue]True[/COLOR]
         [COLOR=darkblue]ElseIf[/COLOR] UCase(Target.Value) = "OTHER" [COLOR=darkblue]Then[/COLOR]
            Rows("19:21").EntireRow.Hidden = [COLOR=darkblue]False[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
      [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
         [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[I heart Excel]

Brilliant, thank you for all your help.

Had a look at UserForm, but this maybe something I need to look into another day!!