Button to Switch between two values on a data validation dropdown list

[olddutch7]

Hello!

I'll try and make this as least confusing as possible.

I have a spreadsheet which will track what multiple employees are working on.

I have a dropdown box in cell C4 using data validation (which is on another sheet, range B3:B4) which has two options, Available and Unavailable. I want to use a command button which will switch back and forth between "Available" and "Unavailable" in cell C4 so the user does not need to use the dropdown menu option.

Essentially, I want to have a "Status" button which will switch between "Available" and "Unavailable" status.

I have attached a photo of the work in progress so far.

Any thoughts or suggestions would be amazing! I have been searching around for this.

 

[Micron]

I take it that you don't have this button in C4 yet but not sure. Sometimes it matters if it's an ActiveX control or a form control so you probably should always state that. Your button click event could be

Private Sub myButton_Click()
If myButton.Caption = "Available" Then myButton.Caption = "Unavailable"
If myButton.Caption = "Unvailable" Then myButton.Caption = "Available"
End Sub

Not sure how that would be of any use, but it does answer your post. Thing is, other than switching the button text, it does nothing.

 

[olddutch7]

Hmmm that's some food for thought..... doesn't quite do what I'm looking for but definitely something to keep in mind as an alternative.

If you look at the square marked "7" in the picture, you will notice the green "Available" option selected which corresponds with the "Station Status" in the middle orange box.
If you look at the square marked "4" I have used the dropdown box to change it to the red "Unavailable" which corresponds with the station in the middle orange box.

In square "7" you'll notice the "Status" button I added. What I want that button to do is switch the green "Available" to the red "Unavailable" which is currently done using a dropdown using the data validation tool.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this it then?

Private Sub myButton_Click()
  With Range("C4")
    If .Value = "Available" Then
      .Value = "Unavailable"
    Else
      .Value = "Available"
    End If
  End With
End Sub

 

[Micron]

So if you click that status button, you want the row with station 7 in the "center block" to become the opposite of what it happens to be at that time?
Or you want what looks to me like merged cells C4:D4 to have the opposite value?
Or the cells are not merged and there is a drop down that you have to set the cell value in square 7 to what you show (Available)?
In the latter case, I'd say that you'd use something like what I posted, but instead use the range address rather than the caption. Something like
sheets("MySheet").range("C4") = "Unavailable"

but using the 2 IF lines idea that I posted first. Note that if you keep the target cell based on a validation list, the value you attempt to set in code in that cell must be in that list. So I'm not seeing the point of a validation list if you go down this path (i.e. you want to avoid validation choices with this button idea).

 

[olddutch7]

Hello and thanks for all the help!

Peter_SSs your code worked perfect. Much simpler than what I was trying to do but has the exact same outcome!

Appreciate all the responses.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

If it is of any use/interest to you, and your Data Validation is a 2-cell range like you have above, then this code would switch between the two values without hard-coding those values.
So it will still switch your Available/Unavailable values but if you altered those two DV list cells on the other sheet to say "red" and "blue" then this same code would switch between those two values.

Private Sub myButton_Click()
  Dim Bits As Variant
  Dim rDV As Range
  
  With Range("C4")
    Bits = Split(.Validation.Formula1, "!")
    Set rDV = Sheets(Mid(Bits(0), 2)).Range(Bits(1))
    If .Value = rDV.Cells(1).Value Then
      .Value = rDV.Cells(2).Value
    Else
      .Value = rDV.Cells(1).Value
    End If
  End With
End Sub