I'm trying to use VBA so that a click of a button will save the worksheet as a particular name (which is linked to a certain cell), however, I want it to canel the save if certain cells are blank.
When doing the code for 1 cell it works fine, however, when I put a range of cells, it gives me a "Run-time error 13: Type mismatch" error.
My code is below:
Private Sub CommandButton1_Click() IF Sheets("Event").range("C4:C24").Value = "" Then Cancel = True MsgBox "Please fill in column C" Else Dim IntialName As String Dim fileSaveName As Variant InitialName = range("c7") fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _ fileFilter:="Excel Files (*.xlsm), *.xlsm") If fileSaveName <> "False" Then Application.DisplayAlerts = False ThisWorkbook.SaveAs (fileSaveName) Application.DisplayAlerts = True End If End If End Sub 2 Answers
Range(...) without property specifying returns its default property .Value.
If a range contains 1 cell, .Value returns scalar value.
If a range contains more than 1 cell, .Value returns 2-dimentional array of Variant. Of course it cannot be assigned into a String type variable or compared with string literal.
Thank you @Mathieu Guindon and @Akina.
I've change my script as follows and now it's working:
Private Sub CommandButton2_Click() Dim myrange As range Set myrange = Worksheets("Event").range("C4:C24") If Application.WorksheetFunction.CountA(myrange) < _ myrange.Cells.Count Then Cancel = True MsgBox "Cells in column C must not be empty." Else Dim IntialName As String Dim fileSaveName As Variant InitialName = range("c7") fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _ fileFilter:="Excel Files (*.xlsm), *.xlsm") If fileSaveName <> "False" Then Application.DisplayAlerts = False ThisWorkbook.SaveAs (fileSaveName) Application.DisplayAlerts = True End If End If End Sub 
