Solved: Preventing Excution of Command

[thomas.szwed]

Hi there,

I have the following statement

[vba]Sub FormatAndPreview()
Dim sSheetName As String

sSheetName = Application.VLookup(Range("K10").Value, Range("F117:G124"), 2, False)

With Sheets(sSheetName)

.Rows.AutoFit
.PrintPreview

End With

End Sub [/vba]

This first part of this fills a sSheetName from Cell K10 in my worksheet. This code runs when a button is clicked. However my users can still select this button when there is no value entered in cell K10. This results in a runtime error, which i certainly dont want to be showing my users!! What code can i add to prevent this code running unless there is a value in K10?

Many Thanks for any help.

[Bob Phillips]

[vba]

Sub FormatAndPreview()
Dim sSheetName As String

sSheetName = Application.VLookup(Range("K10").Value, Range("F117:G124"), 2, False)

With Sheets(sSheetName)
If sSheetName <> "" Then

.Rows.AutoFit
.PrintPreview
End If
End With

End Sub
[/vba]

[thomas.szwed]

Do i need to enter something in here? like vb warning msg?

[Bob Phillips]

Yeah, would be a good idea

[vba]

Sub FormatAndPreview()
Dim sSheetName As String

On Error Resume Next
sSheetName = Application.VLookup(Range("K10").Value, Range("F117:G124"), 2, False)
On Error GoTo 0

If sSheetName <> "" Then

With Sheets(sSheetName)

.Rows.AutoFit
.PrintPreview
End With
Else

MsgBox "No sheet selected", vbOKOnly + vbInformation, "Sheet Preview"
End If

End Sub
[/vba]