[VBA] Naam werkblad is de waarde van een cel

[RoCompy87]

Hallo,

Van VBA heb ik erg weinig verstand, maar op het werk zit ik in Excel met een (volgens mij simpel) probleempje.

In een macro wordt onder andere een nieuw werkblad ingevoegd. Dit werkblad moet als titel de waarde krijgen die op een ander werkblad in cel C1 staat (bijvoorbeeld '36' of '41'). Bij het opnemen van een macro krijg ik dit niet voor elkaar, want dan komt in de VBA-code te staan dat de naam van het nieuwe werkblad '36' moet worden omdat ik dat ten tijde van het opnemen van de macro kopieerde. Iedere keer als de macro wordt uitgevoerd moet echter de waarde die op dat moment in cel C1 staat als werkbladnaam worden genomen. Volgens mij heel simpel, maar ik krijg het met de helpfunctie niet gevonden.

De code is nu - versimpeld - als volgt:

Sub ArchiveerRooster()
'
' ArchiveerRooster Macro
' Met behulp van deze macro wordt het rooster gearchiveerd (alleen waarden blijven behouden).
'

'
    Sheets("blanco plan").Select
    Sheets("blanco plan").Copy Before:=Sheets(3)
    Sheets("bewerk plan").Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "36"
    Sheets("blanco plan (2)").Select
    Sheets("blanco plan (2)").Name = "36"
    Sheets("bewerk plan").Select
    Cells.Select
    Selection.Copy
    Sheets("36").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

En als ik dan nóg een beroep op jullie mag doen... is het ook mogelijk ervoor te zorgen dat er automatisch een melding verschijnt als er al een werkblad met die naam is? Ik zat zelf al te puzzelen met Msgbox ofzoiets... (Bijvoorbeeld: de werkbladnaam moet '38' worden, maar er bestaat al een blad met de naam '38'. De gebruiker wordt hierop gewezen en de macro wordt 'afgebroken'.)

Alvast heel erg bedankt!

 

[etri]

Je kan de volgende code gebruiken:

Public Sub ChangeSheetName
    On Error GoTo ErrHandler
    
    Sheets(2).Name = Sheets(1).Range("A1").Value
    
    Exit Sub
    
ErrHandler:
    If Err.Number = 1004 Then
        MsgBox "Er bestaat al een sheet met deze naam. Voer een andere naam in.", vbOKOnly + vbExclamation, "Er is een fout opgetreden!"
        Sheets(1).Range("A1").Select
    End If
End Sub

Je wijzigt de naam en als dat niet kan dan genereert Excel een fout. Deze fout vang je af, waarna je controleert dat het inderdaad komt door een dubbele naam (fout 1004). Geef in dat geval een melding en selecteer de cel met de naam, zodat de gebruiker de cel-waarde kan wijzigen.

Succes!

 

[RoCompy87]

Ik kan je code redelijk goed volgen, maar ik begrijp niet helemaal waar ik het stukje code nou in mijn macro moet zetten. Waar ik vooral mee zit is namelijk het feit dat het nieuwe werkblad ook beveiligd moet worden, maar de naam van het nieuwe werkblad is dus afhankelijk van de waarde uit cel C1 op een ander werkblad. Ik heb hieronder de 'orginele' code staan, zou iemand mij hiermee nog kunnen helpen? Heb ik dat ook weer geleerd

Sub BewaarPlan()
'
' BewaarPlan Macro
' Met behulp van deze macro wordt de planning van het werkblad 'bewerk plan' gearchiveerd.
'

'
    Sheets("blanco plan").Select
    Sheets("blanco plan").Copy Before:=Sheets(12)
    ActiveSheet.Unprotect
    Sheets("bewerk plan").Select
    ActiveSheet.Unprotect
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "36"      ' Hier begint dus de fout, want 36 moet variabel zijn
    Sheets("blanco plan (2)").Select
    Sheets("blanco plan (2)").Name = "36"
    Sheets("bewerk plan").Select
    Cells.Select
    Selection.Copy
    Sheets("36").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
    ActiveSheet.Shapes("Button 1").Select
    Application.CutCopyMode = False
    Selection.Delete
    ActiveSheet.Shapes("Button 2").Select
    Selection.Delete
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("bewerk plan").Select
    Range("A1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Toelichting: Op het werkblad 'bewerk plan' staan gegevens. Deze gegevens moeten met behulp van deze macro naar een nieuw werkblad worden weggeschreven (als waarden). Dit nieuwe werkblad is een kopie van het bestaande werkblad 'blanco plan'. Het nieuwe werkblad (in beginsel 'blanco plan (2)') krijgt als naam het weeknummer waarop 'bewerk plan', oftewel de te kopieren gegevens, betrekking heeft.

 

[etri]

De code zou er zo uit kunnen zien...

Sub BewaarPlan()
    On Error GoTo ErrHandler

    Sheets("blanco plan").Select
    Sheets("blanco plan").Copy Before:=Sheets(12)
    ActiveSheet.Unprotect
    Sheets("bewerk plan").Select
    ActiveSheet.Unprotect
'    Sheets("blanco plan (2)").Select    'Selecteren is niet nodig
    Sheets("blanco plan (2)").Name = Range("C1").value 'zijnde 36?
    Sheets("bewerk plan").Select
    Cells.Select
    Selection.Copy
    Sheets("36").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
    ActiveSheet.Shapes("Button 1").Select
    Application.CutCopyMode = False
    Selection.Delete
    ActiveSheet.Shapes("Button 2").Select
    Selection.Delete
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("bewerk plan").Select
    Range("A1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    Exit Sub

ErrHandler:
    If Err.Number = 1004 Then
        MsgBox "Er bestaat al een sheet met deze naam. Voer een andere naam in.", vbOKOnly + vbExclamation, "Er is een fout opgetreden!"
        Sheets(1).Range("A1").Select
    End If
   
End Sub

 

[RoCompy87]

Bedankt voor de code!
Maar als [Range("C1").Value] nu niet resulteert in 36, maar in een ander getal, dan loopt de code vast op de regel [Sheets("36").Select] (of werkblad 36 moet al bestaan, maar dan worden de bewerkingen op het verkeerde werblad uitgevoerd, want deze bewerkingen moeten namelijk worden uitgevoerd op het nieuw gemaakte werkblad). Hoe ondervang ik dit?

 

[etri]

Het netst (vind ik) is het gebruik van een variabele, bijvoorbeeld 'sNewSheetName'

Sub BewaarPlan()
    Dim sNewSheetName as string

    On Error GoTo ErrHandler

    --- etc. ---
    
    sNewSheetName = Range("C1").value 'zijnde 36?
    Sheets("blanco plan (2)").Name = sNewSheetName

    --- etc. ---


    Sheets(sNewSheetName).Select

    --- etc. ---

    Exit Sub

ErrHandler:
    If Err.Number = 1004 Then
        MsgBox "Er bestaat al een sheet met deze naam. Voer een andere naam in.", vbOKOnly + vbExclamation, "Er is een fout opgetreden!"
        Sheets(1).Range("A1").Select
    End If
   
End Sub