add warning message box if sheet is not exist

alhagag · Jun 15th 2020

hello

i need help about add message if the sheet isn't existed the code asks me when i add the data which sheet sometimes i make mistake the name should warn me is wrong not existed this sheet i try add this line but the code doesn't work very well it

Code

If whichsheet <> Worksheet Then MsgBox "this sheet is not  available"
Exit Sub

orginal code

Code

Private Sub CommandButton1_Click()
whichsheet = InputBox("in which sheet do you wish enter data ? specify sheet number as  sheet1,sheet2 & sheet3 only .", "sheet number")
If whichsheet = "" Then
MsgBox "you didn't specify a sheet !"
Exit Sub
End If
Worksheets(whichsheet).Activate
 Dim lastrow
 lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 lastrow = lastrow + 1
 Cells(lastrow, 1) = Me.TextBox1
 If Application.WorksheetFunction.CountIf(Range("a2:a" & lastrow), Cells(lastrow, 1)) > 1 Then
 MsgBox "duplicated data ! only unique ids allowed", vbCritical, "remove data"
 Cells(lastrow, 1) = ""
 ElseIf Application.WorksheetFunction.CountIf(Range("a2:a" & lastrow), Cells(lastrow, 1)) = 1 Then
 answer = MsgBox("are you sure want to add the record?", vbYesNo + vbQuestion, "add recor")
 If answer = vbYes Then
 Cells(lastrow, 1) = Me.TextBox1
 Cells(lastrow, 2) = Me.TextBox2
 Cells(lastrow, 3) = Me.TextBox3
 Cells(lastrow, 4) = Me.TextBox4
 Cells(lastrow, 5) = Me.TextBox5
 End If
 End If
End Sub

Display More

Fluff13 · Jun 15th 2020

How about

Code

If Not Evaluate("isref('" & whichsheet & "'!A1)") Then
   MsgBox "Sheet " & whichsheet & " does not exist"
   Exit Sub
End If
Worksheets(whichsheet).Activate

**royUK** · Jun 15th 2020

Why have three identical sheets for data entry. No wonder you are starting to have issues- all related data should be stored on one sheet. If you have multiple sheets then prevent having to check by making the user select a sheet first!

I have added a ComboBox to select the sheet from

The add button will not be usable until a sheet is selected.

Your code needs the sheet to be activated which is inefficient. I have edited the code to work without activating the sheet.

alhagag · Jun 15th 2020

hi, Fluff awesome your adjusting this what i'm looking for thanks so much

Fluff13 · Jun 15th 2020

You're welcome & thanks for the feedback.

alhagag · Jun 15th 2020

hi, Royuk you wrote some words incorrectly ;i corrected it doesn't matter but what's the difference between two files actually i don't check carefully it get me error runtime 91 in this line

Code

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1

**royUK** · Jun 15th 2020

There is no difference, I'm not sure why it attached twice.

Sorry,I missed a line of code.

alhagag · Jun 15th 2020

thanks, Royuk now your file works

**royUK** · Jun 15th 2020

You don't need to check if the sheet exists with my amended userform

add warning message box if sheet is not exist

Participate now!

Share