asked on

Problems with making a index page from values in my msgbox.

Im new to VBA and got some problems with making a index page from values in my msgbox.

dont under stand why this 2 lines of codes dosent work.

Set wsName = Me.BDClass.Value

Open in new window

and this one

shIndex.Range("B5").End(xlDown).Offset(1, 0).Select

Open in new window

Private Sub AddFromBtn_Click()

    Dim r As Range
    
    'Checks if BDClass allready exist
    If BDClass.Value <> "" Then
        Set r = wsIndex.Range("B:B").CurrentRegion.Find(BDClass.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en klassifikation med samme navn")
                Exit Sub
            End If
        Else
            MsgBox ("Der skal angives klassifikation")
            Exit Sub
    End If
    
    'Checks if BDName allready exist
    If BDName.Value <> "" Then
        Set r = wsIndex.Range("C5").CurrentRegion.Find(BDName.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en bygningsdel med samme navn")
                Exit Sub
            End If
        Else
            MsgBox ("Der skal angives bygningsdel navn")
            Exit Sub
    End If
    
    
    'Creates new sheet
    Worksheets("Temp").Copy After:=Worksheets("Index") 'copy Temp to after Index
        ActiveSheet.Name = BDClass.Value 'Renames the sheet to BDClass value
        
    
    'Writes value to new sheet
    ActiveSheet.Range("C3") = BDClass.Value
    ActiveSheet.Range("E3") = BDName.Value
    
    
    'Writes value to Index sheet
    Dim shIndex As Worksheet
    
    Set shIndex = ThisWorkbook.Sheets("Index")
    Set wsName = Me.BDClass.Value '<-------------- Error----------------
    
    shIndex.Range("B5").End(xlDown).Offset(1, 0).Select  '<---------Error----------
        ActiveCell.FormulaR1C1 = "='" & wsName & "'!R[-3]C[1]"
        ActiveCell.Hyperlinks(1).SubAddress = "'" & wsName & "'!A1"
            ActiveCell.Offset(0, 1).FormulaR1C1 = "='" & wsName & "'!R[-5]C[1]"
                
    'Call wsSort '<----------Not tested-----------

    Unload Me
       
    
End Sub

Open in new window

Subodh Tiwari (Neeraj)

The first line doesn't work because that's not how a Sheet variable is set. And the second line may not work for two reasons, either there is no data below B5 and xldown will select the last cell in column B and further offset 1 more row down is not possible from there OR currently shIndex is not the active sheet so select any cell on an inactive sheet will throw an error.
It's always better to avoid select/activate unless very much required.

Also, you should write Option Explicit on top of the Module so that you are bound to declare all the variables you use in the code.

See if the following tweaked lines work for you...

'Writes value to Index sheet
Dim shIndex As Worksheet
Dim wsName As Worksheet
Dim lr As Long
Dim Rng As Range

Set shIndex = ThisWorkbook.Sheets("Index")
Set wsName = ThisWorkbook.Sheets(Me.BDClass.Value)

lr = shIndex.Cells(Rows.Count, "B").End(xlUp).Row + 1
Set Rng = shIndex.Range("B" & lr)

With Rng
    .FormulaR1C1 = "='" & wsName.Name & "'!R[-3]C[1]"
    .Hyperlinks(1).SubAddress = "'" & wsName.Name & "'!A1"
    .FormulaR1C1 = "='" & wsName.Name & "'!R[-5]C[1]"
End With

Open in new window

Christian Græsborg

ASKER

Still getting an error at

Set wsName = ThisWorkbook.Sheets(Me.BDClass.Value)

Open in new window

I have included the excel file, if someone can help me with it
bygningsdel_journal-N03.xlsm

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.