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 Sub2 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
