Excel button to move between sheets and create a pop up error

[xVikesh]

Hello everyone,

My first time posting on this forum so please excuse any mistakes I make.

I have created a macro button to move from one sheet to another in an excel document but I want also want the button to check to see if multiple ranges of cells have values, if they don't then pop up a message and don't run the rest of the macro. I have not used vba much at all so if someone can help i would much appreciate it. Below is basically what I want to say but not in a vba format.

If the cell range (B2:B16), (E2:E6) and (E10:E15) from a sheet called Student calculator contains any values, then continue onto the sheet called Student calculator Results. If any of the cells do not contain a value, then return a message box saying "error - all information has not been inputted" and do not continue onto the other sheet.

Also if I wanted to choose a cell range from another sheet, how would i go about doing this?

Thanks in advance

 

[HappyMeal]

Hi xVikesh,


In any column add this formula '=IF(OR(B1="",B2="",B3="",B4="",B5="",B6="",B7="",B8="",B9="",B10="",B11="",B12="",B13="",B14="",B15="",B16="",E1="",E2="",E3="",E4="",E5="",E6="",E10="",E11="",E12="",E13="",E14="",E15=""),"Blanks","No Blanks")'


It will tell you if there are any blanks in the cells you have specified.


In the following code i have placed the formula in K1, you will need to change it if you place it anywhere else.

Sub mcrStudentCalc()
' mcrStudentCalc Macro
Dim Blanks As String
Blanks = Range("K1").Value


If Blanks = "No Blanks" Then
Sheets("Student Calculator Results").Select
Range("B2:D9").Select
Else
MsgBox "error - all information has not been inputted"
End If
End Sub

Also if you want to select cells in another tab you must first select the tab then select the range (below for example)


Sheets("Student Calculator Results").Select
Range("B2:D9").Select


Hope this helps


Your friendly Neighbourhood HappyMeal

 

[Domenic]

Or, maybe something like this...

    [COLOR=darkblue]Dim[/COLOR] rArea [COLOR=darkblue]As[/COLOR] Range

    [COLOR=darkblue]With[/COLOR] ActiveWorkbook.Worksheets("Student calculator")
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rArea [COLOR=darkblue]In[/COLOR] .Range("B2:B16,E2:E6,E10:E15").Areas
            [COLOR=darkblue]If[/COLOR] Application.CountBlank(rArea) > 0 [COLOR=darkblue]Then[/COLOR]
                MsgBox "Error - all information has not been inputted.", vbCritical, "Error"
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] rArea
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=green]'etc...[/COLOR]

Hope this helps!

 

[xVikesh]

Thanks to both of you, I done it through HappyMeal's way and it worked a charm.