none
Macro 2 RRS feed

  • Question

  • Hi experts...

    I need a macro which will give me a pop-up window then the user should enter a tab name and then he should go to that tab?

    Please help and thank you...


    Thanks you very much

    Monday, September 3, 2012 12:43 PM

Answers

  • You should be able to adapt the macro from the other thread for this.

    That macro:

    Sub Row_to_delete()
    Dim no, x&
    no = InputBox("Give me a no off your database record from A column", "Delete date")
    If IsNumeric(no) = False Then MsgBox "Operaction fail - number is req", vbCritical
    For x = Cells(Rows.Count, "m").End(xlUp).Row To 1 Step -1
        If Cells(x, "m").Value = no + 0 Then Range(Cells(x, "a"), Cells(x, Columns.Count)).Clear
    Next x
    End Sub
    Well, we don't need to check that the input is numeric, so we can remove the msgbox line.


    All that's left is to replace the search and remove function with a worksheet.activate line:

    Sub Switch_Sheet()
    name = InputBox("Give me a sheet name")
    worksheets(name).activate
    End Sub

    Of course,  you'll want to add an "On Error" line or a routine to check that the sheet exists and give an error message if not. And you ought to explicitly declare your variables as in the example above, but I'm sure you can work out how to do that.





    Monday, September 3, 2012 1:04 PM

All replies

  • You should be able to adapt the macro from the other thread for this.

    That macro:

    Sub Row_to_delete()
    Dim no, x&
    no = InputBox("Give me a no off your database record from A column", "Delete date")
    If IsNumeric(no) = False Then MsgBox "Operaction fail - number is req", vbCritical
    For x = Cells(Rows.Count, "m").End(xlUp).Row To 1 Step -1
        If Cells(x, "m").Value = no + 0 Then Range(Cells(x, "a"), Cells(x, Columns.Count)).Clear
    Next x
    End Sub
    Well, we don't need to check that the input is numeric, so we can remove the msgbox line.


    All that's left is to replace the search and remove function with a worksheet.activate line:

    Sub Switch_Sheet()
    name = InputBox("Give me a sheet name")
    worksheets(name).activate
    End Sub

    Of course,  you'll want to add an "On Error" line or a routine to check that the sheet exists and give an error message if not. And you ought to explicitly declare your variables as in the example above, but I'm sure you can work out how to do that.





    Monday, September 3, 2012 1:04 PM
  • Hi Ben,

    Thank you the macro works well.

    If I press cancel how can i stop the macro?


    Thanks you very much

    Monday, September 3, 2012 1:33 PM
  • If the inputbox returns false then it has been cancelled or has had no input.

    You could insert something like:

    if name = false then
    '...end program
    else
    '... rest of program
    Alternatively, use StrPtr(name) to see if the variable contains any data. If the userform has been cancelled it should be return 0.

    Monday, September 3, 2012 1:54 PM