1 drop down box Changes another

[ScottyG]

I have 2 dropdown boxes side by side, each with 2 selection choices. The left one: First Name, Home Phone. The right one: Last Name, Work Phone.

Is there a formula so that if the user selects an item from either box, the corresponding item will be entered in the other box, thus saving them one mouse click?

Thanks for any help!

Scotty

 

[HalfAce]

You're most welcome. Glad it helped.

And yes, (as I always say sometimes), "merged cells is the devil!" :x
If you can't get away with unmerging them as Norie suggests, then what are the cells
that are actually merged?
(From Y1 to what, and from AC1 to what?)
Also, what I was thinking is if the user will choose one and then be entering data
into the cell below that, maybe we should have it auto select a cell (perhaps the one below
the one a choice was made in?) to get rid of the validation dropdown
arrow and save them one more click as I believe that was the reason for your original
request, right?
If this sound good then which cell would you want to have selected?

 

[ScottyG]

Yes, it is an existing application with plenty of merged cells that can't be unmerged or changed at this point. Let me guess, you have to create new code to unmerge the cells, then remerge them or something like that?

 

[HalfAce]

Nope, that's not what I have in mind. (Though that could be done.)
When referring to merged cells in code you can usually get away with referring to
the entire range that is merged together.
For example, if Y1:Z3 are have been merged together, you would refer to Range("Y1:Z3")
instead of just Y1.

So, what are the entire ranges of these two merged ranges you're interested in?

 

[ScottyG]

Ahh HalfAce, thanks again!

The first dropdown box is actually in cell Y1 not A1 from the example and I merged Y, Z, AA, and AB. The 2nd dropdown box is actually in AC1, not B1, and I merged AC,AD, and AE.

As far as the auto select from your previous post, it seems to work perfect the way it does now, just not for the merged cells.

 

[HalfAce]

Try this slightly amended version of the same code.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("Y1:AE1")) Is Nothing Then Exit Sub

If Not Intersect(Target, Range("Y1:AB1")) Is Nothing Then
  Application.EnableEvents = False
  Select Case Target.Value
    Case "First Name"
      Range("AC1:AE1").Value = "Last Name"
    Case "Home Phone"
      Range("AC1:AE1").Value = "Work Phone"
  End Select
End If

If Not Intersect(Target, Range("AC1:AE1")) Is Nothing Then
  Application.EnableEvents = False
  Select Case Target.Value
    Case "Last Name"
      Range("Y1:AB1").Value = "First Name"
    Case "Work Phone"
      Range("Y1:AB1").Value = "Home Phone"
  End Select
End If

Application.EnableEvents = True
End Sub

 

[ScottyG]

Re

thanks again, that worked perfect and now I think I understand a bit more.

Scotty

 

[HalfAce]

You're welcome. Always happy to impart a little understanding.
(A little understanding can be dangerous!)

To avoid using merged cells in the future you can select your multiple columns and
use Format > Cells > Alignment tab >and from the 'Horizontal' dropdown, choose
Center across selection. :wink:

 

[ScottyG]

followup

What if I already have a routine for that worksheet with the same title:

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, Range("$AX$45:$AX$52")) Is Nothing Then Exit Sub
S3SS.PartsPricingChange

End Sub

Will your routine still work? Or do I have to give it a new name or incorporate it into this existing routine?

Thanks again and I still have so much to learn!

Scott

 

[HalfAce]

Yes, they both need to be incorporated into one single routine.
Something like this should work.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("$AX$45:$AX$52")) Is Nothing Then
  S3SS.PartsPricingChange
End If

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("Y1:AE1")) Is Nothing Then Exit Sub

If Not Intersect(Target, Range("Y1:AB1")) Is Nothing Then
  Application.EnableEvents = False
  Select Case Target.Value
    Case "First Name"
      Range("AC1:AE1").Value = "Last Name"
    Case "Home Phone"
      Range("AC1:AE1").Value = "Work Phone"
  End Select
End If

If Not Intersect(Target, Range("AC1:AE1")) Is Nothing Then
  Application.EnableEvents = False
  Select Case Target.Value
    Case "Last Name"
      Range("Y1:AB1").Value = "First Name"
    Case "Work Phone"
      Range("Y1:AB1").Value = "Home Phone"
  End Select
End If

Application.EnableEvents = True
End Sub

(What is 'S3SS.PartsPricingChange'? - Another sub that's being called?)

 

[ScottyG]

re

Thanks again! That did the trick. And yes that is another routine being called. This is going to be a custom made Invoice.

Scotty