PDA

View Full Version : Solved: Preventing Excution of Command



thomas.szwed
09-05-2008, 02:21 AM
Hi there,

I have the following statement

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

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
09-05-2008, 02:27 AM
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

thomas.szwed
09-05-2008, 02:31 AM
Do i need to enter something in here? like vb warning msg?

Bob Phillips
09-05-2008, 02:38 AM
Yeah, would be a good idea



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