Previous Topic Next Topic

Need macro help

Closed
bbrown123 Posts 2 Registration date Wednesday February 12, 2014 Status Member Last seen February 14, 2014 - Feb 12, 2014 at 12:01 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Feb 14, 2014 at 11:31 PM
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 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Feb 13, 2014 at 11:26 PM
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





.
0
bbrown123 Posts 2 Registration date Wednesday February 12, 2014 Status Member Last seen February 14, 2014
Feb 14, 2014 at 07:48 AM
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?
0
Answer 2 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Feb 14, 2014 at 11:31 PM
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.
0

Similar discussions

Automatically Run Macro from a calculated cell value change
smiller -
TrowaD -

1 response
Execute Macro On Calculated Cell change
Notar -
rizvisa1 -

5 responses
Code for macro that conditionally inserts rows
bfleming -
bfleming -

4 responses
How to convert number into text in Excel
boratechandu -
Raj -

9 responses