excel - How to automatically create a link within a MENU to a specific tab? -

excel - How to automatically create a link within a MENU to a specific tab? -

just want start off saying on rating of 1-10, knowledge of vba approximately -2. i’m learning go , count on lot of existing coding.

my issue, have workbook 2 tabs:

tab 1 = menu (contains listing of tabs , links them) tab 2 = template form (hidden)

within menu, created button calls module allows user create new tab. when clicked, hidden template form made visible can copied on new tab , hidden again.

the user prompted come in business programme name, value carried on specific cell within newly created worksheet.

the user prompted come in acronyme, value carried on 2 places:

1) specific cell within worksheet 2) becomes name of tab

this code used:

sub new_prog() dim tmp_sheet worksheet sheets("template").visible = true sheets("template").copy after:=sheets(sheets.count) set test = activesheet dim prog_full_name string prog_full_name = inputbox("what program's total name", "program total name") activesheet.range("c6").value = ucase(prog_full_name) dim prog_acro string prog_acro = inputbox("what program's acronyme", "program acronyme") activesheet.range("g6").value = ucase(prog_acro) activesheet.name = ucase(prog_acro) dim prog_dpt string prog_dpt = inputbox("what program's section (esdc, tb, etc.)", "program department") activesheet.range("k6").value = ucase(prog_dpt) sheets("template").visible = false**

question 1 code should suing module verify if tab acronyme exists , homecoming error message advising user.

question 2 i’m looking have link automatically created within cell in menu tab links new tab created. how pass tab name specific cell, next empty cell , create hyperlinkable actual tab?

question 3 in addition, in menu tab, have column called business , 1 called client; user select if link should created under business colum, client column or both.

thanks.

try 'bare-bones' solution addresses principles require. illustration assumes simple inputboxes user entry, tab names in menu sheet in col mencol , hyperlink columns mencol+1 business , mencol+2 client.

sub soq() dim wsmen worksheet, wsnew worksheet dim nextmenrow long, mencol long set wsmen = sheets("menu") mencol = 1 usrws = inputbox("enter tab name") each ws in worksheets if ucase(usrws) = ucase(ws.name) msgbox "this tab name exists" exit sub end if next ws lnk = inputbox("create link under (a), business (b) or client (c)") activeworkbook.sheets.add after:=worksheets(worksheets.count) activesheet.name = usrws wsmen nextmenrow = .cells(.rows.count, mencol).end(xlup).row + 1 .cells(nextmenrow, mencol).value = usrws select case ucase(lnk) case = "a" phone call createhyp(usrws, nextmenrow, mencol + 1) phone call createhyp(usrws, nextmenrow, mencol + 2) case = "b" phone call createhyp(usrws, nextmenrow, mencol + 1) case = "c" phone call createhyp(usrws, nextmenrow, mencol + 2) end select end end sub sub createhyp(byval sht string, byval rw long, byval co long) sheets("menu") .hyperlinks.add anchor:=.cells(rw, co), address:="", subaddress:=sht & "!" & cells(1, 1).address, texttodisplay:="goto " & sht end end sub

excel vba excel-vba