Seite 1 von 1

VBA string + variable soll sheet codeName ergeben

BeitragVerfasst: 17. Mai 2019, 22:59
von RobbiDalmann
Hallo ihr Retter,

ich werde hier noch verrückt.

Ich versuche seit einer Ewigkeit über VBA Sheets zu aktivieren, die über den codename angesprochen werden sollen. Der codename soll aber eine Variable beinhalten.

Code: Alles auswählen
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim KlickZeile As Long
Dim KlickNr As Long
Dim CNstr As String
Dim CNedWks As Worksheet

    If Target.Column = 1 And Target.Row >= 4 And Target.Row <= 33 Then
        KlickZeile = Mid(ActiveCell.Address, 4)
        KlickNr = KlickZeile - 3
       
        CNstr = "SuS" & KlickNr
        Set CNedWkb = CNstr
        CNedWkb.Activate
    End If   
End Sub



Schritt für Schritt:

Man stelle sich ein Workbook vor, dessen sheet.names völlig variabel bleiben sollen (also kein sheets.name verwendbar). Deshalb codeName:

1. man klickt in Sheet0 auf eine Zelle im Bereich A4:A33
2. die aktivierte Zelle wird erkannt und die Zeilennummer ermittelt (KlickZeile)
3. die Verschiebung der Range wird kompensiert mit -3. So entsteht die (KlickNr) 1 bis 30
4. nun soll über den codeName das entsprechend "sheet1", "sheet2", "sheet3", "sheet4".... oder "sheet30" aktiviert werden.
3. fertig - mehr nicht (ein Hyperlink im Wokrbook der über das Aktivieren einer bestimmten Zelle funktioniert)

Das Problem ist aber, dass ich ja nur einen String generiere, wenn ich eine Variable für den Zelleninhalt mit einbauen möchte. VBA möchte aber ein Objekt.

Wie kann ich den Code zum Laufen bringen???

Re: VBA string + variable soll sheet codeName ergeben

BeitragVerfasst: 17. Mai 2019, 23:55
von losgehts_
Hallo,

meines Wissens nach (kanns gerade nicht ausprobieren), bleibt dir nichts anderes übrig, als alle sheets zu durchlaufen und die Codenames mit deinem String zu vergleichen:

Code: Alles auswählen
function getSheetByCodeName(sCodeName as string, Wb as workbook) as sheet
dim sh as variant
for each sh in wb.sheets
   if sh.Codename = sCodeName then
      set getSheetByCodeName = sh
      exit function
   end if
next
end function
Achtung: der Code ist ungetestet (habe gerade kein Excel zur Hand).

Diese Funktion könntest du dann einfach so in deinem Code aufrufen:
Code: Alles auswählen
        Set CNedWkb = getSheetByCodeName(CNstr, activeworkbook)


Was mir sonst noch auffällt:
Statt
Code: Alles auswählen
KlickZeile = Mid(ActiveCell.Address, 4)
könntest du direkt die Range.Row-Eigenschaft der Zelle benutzen:
Code: Alles auswählen
KlickZeile = ActiveCell.row
das funktioniert dann auch wenn die aktive Zelle z.B. AA1 ist.

Grüße, Ulrich

Re: VBA string + variable soll sheet codeName ergeben

BeitragVerfasst: 18. Mai 2019, 06:35
von losgehts
Hallo,

sorry, oben ist ein kleiner Fehler: die Funktion muss so deklariert sein:
Code: Alles auswählen
Function getSheetByCodeName(sCodeName As String, Wb As Workbook) As Object


Grüße, Ulrich

Re: VBA string + variable soll sheet codeName ergeben

BeitragVerfasst: 18. Mai 2019, 10:40
von RobbiDalmann
Hallo Ulrich,

vielen Dank für den Gedankenanstoß/Lösungsweg und den Code.
Ich habe den Code eingebaut und alles funktioniert jetzt prima.

Meine Benken, dass das Durchlaufen aller 38 Sheets eine zu starke Verzögerung zeigt (soll ja einen Hyperlink ersetzen), waren umsonst.
Man merkt fast keine Verzögerung (zumindest auf meinem Rechner)

Besten Dank
Robbi

Re: VBA string + variable soll sheet codeName ergeben

BeitragVerfasst: 18. Mai 2019, 12:03
von snb
Code: Alles auswählen
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Range("A4:A33"), Target) Is Nothing Then
    For Each it In Sheets
      If it.CodeName = "Sus" & Target.Row - 3 Then Exit For
    Next
    it.Activate
  End If
End Sub