VBA string + variable soll sheet codeName ergeben

Moderator: ModerationP

VBA string + variable soll sheet codeName ergeben

Beitragvon RobbiDalmann » 17. Mai 2019, 22:59

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???
RobbiDalmann
 

Re: VBA string + variable soll sheet codeName ergeben

Beitragvon losgehts_ » 17. Mai 2019, 23:55

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
losgehts_
 

Re: VBA string + variable soll sheet codeName ergeben

Beitragvon losgehts » 18. Mai 2019, 06:35

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
Ich freue mich über jede Art von Feedback
Die Forensoftware sendet mir aktuell keine Benachrichtigungen mehr zu, daher kann es gut sein, dass ich deine Antwort auf meinen Post nicht mitbekomme.
losgehts
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 4627
Registriert: 03. Okt 2006, 14:08

Re: VBA string + variable soll sheet codeName ergeben

Beitragvon RobbiDalmann » 18. Mai 2019, 10:40

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
RobbiDalmann
 

Re: VBA string + variable soll sheet codeName ergeben

Beitragvon snb » 18. Mai 2019, 12:03

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
snb
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6807
Registriert: 25. Sep 2014, 16:37


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 16 Gäste