Problem solving macros

[SamanthaSkilltec]

Hi all

I'm about to have a genuine emotional breakdown over a spreadsheet I've created for work and I'm really hoping someone can help me work out wth is going on and how to fix issues I'm having please.

In short, I work at a training company and have created a spreadsheet with multiple templates for the different course packages. The pages are hidden to (try to) protect them from being accidentally edited, but because they're hidden, I couldn't hyperlink them, so have created a button for each which is supposed to go directly to the relevant page. First problem is here - this works with no problem at all on my PC but no one else is able to get them to work, meaning they have to manually unhide the page.... any ideas please?

Secondly, on each template page there is a button to 'Create a New Student'. This is the code for the macro that it's linked to:

Sub CreateNewStudent()
'
' Macro3 Macro
' Dim test As Worksheet
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    Set test = ActiveSheet
    test.Name = "New Student"


    ActiveSheet.Shapes.Range(Array("Button 4")).Select
    Selection.OnAction = "Confirm"
    Selection.Characters.Text = "CONFIRM STUDENT"
    With Selection.Characters(Start:=1, Length:=15).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("A1:E1").Select
    ActiveSheet.Shapes.Range(Array("Button 5")).Select
    Selection.delete
    End Sub

This button was working PERFECTLY! Until it didn't... now it keeps stopping at test.Name = "New Student".

This happened after I tried to insert code to call another macro just after this part of the code, that would hide the template before making changes on the newly created 'New Student' sheet because when my colleague was working on the spreadsheet she did exactly what I feared and accidentally renamed some of the templates... I've reinstated these now but this one is just not having it, even though I've undone the change I made and it's exactly as it was before.

This whole worksheet worked perfectly on my PC and problems began when my colleagues have accessed it via our Server... just things not working properly. The first issue I had was to do with some link to another workbook called Flowers NEW... I had renamed the worksheet to Flowers when I moved it to the server but now changed it back because no matter what I did, we could not get rid of this message about links, despite me breaking the link, changing the source etc etc etc....

Sorry to be a whinge - I've just spent SOOO much time on this and no one else is able to help me at work, but we're relying on this spreadsheet to replace our student monitoring procedure, so it's really important.

Thanks in advance for any advice you can give.

Samantha

 

[Peter_SSs]

... now it keeps stopping at test.Name = "New Student".

Just stops ... or stops and gives an error message? If a message, what is the message?

 

[Repush]

You left some important info out . .
This code just copies the sheet, make the button refer to a macro "Confirm" and applies some button make up.
Then it removes a "button 5"?!?
So no hiding / unhiding or all of the other stuff you mentioned.

Q: is there a "Button 4" existing on the sheet?

 

[SamanthaSkilltec]

Hi guys, thanks for coming back to me.

The message is 'Run-time error 1004. That name is already taken. Try a different one'... smacking my head now as I know what the problem is. It really does help if you READ the message and not just click on Debug!!

Sorry to have wasted your time on that one.

Any ideas on why the buttons aren't working for my colleagues, though? Half the problem is that they're manually unhiding the templates then making changes to the template by accident without creating a new student.

Samantha

PS. Have been for a walk to calm down (Macros drive me to the brink!!) and had some lunch so able to think straighter now.