Need macro help [Closed]

Report
Posts
2
Registration date
Wednesday February 12, 2014
Status
Member
Last seen
February 14, 2014
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello; thanks for viewing this question....

I need help creating a macro, with detailed instructions on how to put it into excel and actually make it work.

I want to have a macro set up that allows users to click in a cell, type in data and that whatever was typed in creates a sheet named whatever was typed in that cell, and hide it from view. (will need to run this macro on sheet 2 through sheet 11).

In addition, I want that same cell, to turn the data into a hyper link that when clicked on opens up the sheet that was created.

All new sheets created in this manner will share the same format; Im going to have an "inspection" sheet that is general and can be used as the format for all newly created cells.

Basically, what I am doing is creating 9 sheets in the work book (sheets 2 - 9). Each sheet will be named after a category. For example, Sheet 2 would be named Generators. The user would go to that sheet to view a list of generators, or, add a generator to the list. If they add a generator to the list on that sheet, and name it Generator 3, the word Generator 3 becomes a hyperlink, a new sheet is auto created that is named "Generator 3" (the new sheet will be in the inspection sheet format that I create) and that sheet is hidden from view; well until someone clicks on the cell that has the hyperlinked "Generator 3" in it.

Each page 2-11 would also need a macro that allows the user to print all sheets that are hyperlinked to on that page. A radio button that says print reports or something would be awesome.

Can anyone help? Thanks!

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
let us question by question.
for e.g
"I want to have a macro set up that allows users to click in a cell, type in data and that whatever was typed in creates a sheet named whatever was typed in that cell, and hide it from view. (will need to run this macro on sheet 2 through sheet 11).
"


open vb editor (alt F11)
hit control R
you see name of YOUR WORKBOOK
under the name click "thisworkbook"
a window opens
in that window copy this EVET CODE and the FUUNCTION.
save the file as macro enable. close and open and enable macros

I presume there are some names in column A of each of the sheets
select any name (you need not click anything)
see what happens.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim shname As String
Set Sh = ActiveSheet
If Target.Column <> 1 Then GoTo enableevents
Application.enableevents = False
shname = Target
If SheetExists(shname) Then GoTo enableevents
Worksheets.Add
ActiveSheet.Name = Target
ActiveSheet.Visible = xlSheetHidden
MsgBox "new sheet opened and hidden "
enableevents:
Application.enableevents = True
End Sub

Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function





.
Posts
2
Registration date
Wednesday February 12, 2014
Status
Member
Last seen
February 14, 2014

I tried it but I got an error message and the first line was highlighted as it told me no specified range for sub or function.

I really need to be walked through this like I am a moron, because I truly do not know what I am doing lol.

Thank you so much for helping. I hope this works! What do I do now?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
your configuration of sheet may be different from what I envisaged. Tt worked in my sheet. SEND YOUR WORKBOOK (PRERERABLY)SMALL AMOUNT OF DATA) .
HOW?
clilck speedyshare.com
upload your file
they will give you a web address
email thAt address \
and AGAIN EXPLAIN WHAT YOU WANT.