Solved Hide/Unhide Command Button with save file option

August 9, 2017 at 22:19:08
Specs: Windows 10
I wish to have a command button that can hide/unhide with various safe file option based on drop down list value in G6. Here is a table:

C2 : Open <hide Command Button>
C3 : In Progress <unhide Command Button and save file only>
C4 : Cancel <unhide Command Button and save & close file immediately>
C5 : Close <unhide Command Button and save & close file immediately>
G6 : Drop Down List box

Appreciates the helps. TQ.

See More: Hide/Unhide Command Button with save file option

Reply ↓  Report •

August 10, 2017 at 06:32:00
I'm confused by your requirements. I don't understand what the Command Buttons are supposed to do.

I'm also confused by the fact that you listed 4 cells/4 Command Buttons.

First Confusion:

Obviously this is going to require a macro to monitor the Drop Down in G6 and perform certain actions based on what value is chosen. I don't understand what the hiding and unhiding of the Command Buttons accomplishes.

For example, you said: "C4 : Cancel <unhide Command Button and save & close file immediately>"

What is the point of unhiding the Command Button in C4 if the code is going to "save & close file immediately"? Is it so that the Command Button will be there the next time the workbook is opened? If so, what is the C4 Command Button supposed to do? Will there be different macro assign to each Command Button?

Second Confusion:

Let's say "Open" is chosen from the Drop Down in G6. You say that you want to "<hide Command Button>" in C2. What about the Command Buttons in C3:C5?

The same question applies to the other Drop Down choices. I don't understand how each choice impacts the Command Buttons in the other cells.

Please keep in mind that we can't see your spreadsheet from where we are sitting nor are we familiar with your process. You will need to supply some more details before we can offer any suggestion.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

August 10, 2017 at 20:36:04
Sorry to confused you. What I need is only one Command Button that applied to all function based from G6 drop down list value.
Hiding Command Button on 'Open' status is to avoid user create file without data.
Unhide Command Button and save on 'In Progress' status is to let the user put file on hold after data in mandatory cells are filled and to countiue updating data when received the latest updated
Unhide Command Button, save and quit on 'Close' and 'Cancel' status when data entred was completed.
Hope you understand what I need.

Reply ↓  Report •

August 24, 2017 at 05:37:58
✔ Best Answer
Since nobody willing to solve my question, here is my answer for my own question that I have figured it out for whom looking for an answer on the same question.

I added an extra code for create filename based on B6 & E6 value. Take note that " " in >newFile = " " & fName & " " & vName< was purposely added for make a space in filename. I am glade to share the code here:

Private Sub CommandButton1_Click()
Dim newFile As String, fName As String, vName As String
fName = Range("B6").Value
vName = Range("E6").Value
tSave = Range("G6").Value
newFile = " " & fName & " " & vName
If tSave = "In Progress" Then
ThisWorkbook.SaveAs "Z:\TRAINING\" & Format(Date, "ddmmyyyy") & newFile
ThisWorkbook.SaveAs "Z:\TRAINING\" & Format(Date, "ddmmyyyy") & newFile
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
With Me
If .Range("G6").Value = "Open" Then
.CommandButton1.Visible = False
.CommandButton1.Visible = True
End If
End With
End Sub
Private Sub Worksheet_Activate()
Application.EnableEvents = False
With Me
.CommandButton1.Visible = True
.Range("G6").Value = 1
End With
Application.EnableEvents = True
End Sub

Reply ↓  Report •
Related Solutions

Ask Question