Clever-Excel-Forum

[my first button (Schaltfläche 83)][1]


[other button which I want to activate vom "Hirata Bestellformular"
1st option: the top blue one
2nd option: the big one (activexelement) ][2]


  [1]:
Dateiupload bitte im Forum! So geht es: Klick mich!

  [2]:
Dateiupload bitte im Forum! So geht es: Klick mich!


Hello guys, this is my first post and I#m quite a beginner but Ill try my best:)
I want to activate one button from another worksheet because I want to execute the code below on the worksheet "Teileliste" directly from "Hirata Bestellformular". How can I accomplish this? Do you guys have an idea?

Kind regards 
Patrick

    Sub Teileliste_generieren()

' advanced filter

    Sheets("Hirata Bestellformular").Range("Tabelle3[[#Headers],[#Data]]"). _
        AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B50:B51"), _
        CopyToRange:=Range("B54:B55"), Unique:=False
           
        Range("B5").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(LEFT(R[50]C,FIND(CHAR(10),R[50]C)-1),R[50]C)"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(LEFT(R[50]C,FIND(CHAR(10),R[50]C)-1),R[50]C)"
    Range("B5:B6").Select
    Selection.AutoFill Destination:=Range("B5:B26"), Type:=xlFillDefault
    Range("B5:B26").Select
    Range("D22").Select

'formatierung tabelle

    Range("B3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16763955
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("B4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternThemeColor = xlThemeColorAccent3
        .Color = 16777215
        .TintAndShade = 0
        .PatternTintAndShade = 0.799981688894314
    End With
    Range("B5").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 9868950
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("B6").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15395562
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("B5:B6").Select
    Selection.AutoFill Destination:=Range("B5:B26"), Type:=xlFillDefault
    Range("B5:B26").Select
    Range("D20").Select
    
' Wenn 0 blank
    
    Range("B5:B26").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C22").Select
    
    ' Exportieren
    
        ThisWorkbook.Sheets("Teileliste").Copy
    
    Application.GetSaveAsFilename
    
    End Sub

Hi,

ich habe die Links aus Sicherheitsgründen zu dem Fremdhoster deaktiviert. Bitte lade Dateien direkt im Forum hoch. Wie das geht, kannst du hier nachlesen: http://www.clever-excel-forum.de/Forum-N...gshinweise . Schau dir bitte die mit Wichtig gekennzeichneten Beitrage an.

Hello Patrick,

I think, you want to execute p.E
from another sheet?

you can write


Important is the Point before every Range or Cells you want to use with With. If you interleave ? With, the "pointed range code" is in relationship to the last With.

hellownero..
Hello guys, this is my first post ...

 
Hello, are you sure..? what is that..? http://www.clever-excel-forum.de/Thread-...8#pid88438

crossposted:
 
https://www.excelforum.com/excel-program...heets.html

und
https://www.reddit.com/r/excel/comments/...orksheets/

(07.08.2017, 21:38)snb schrieb: [ -> ]crossposted:
 
https://www.excelforum.com/excel-program...heets.html

und
https://www.reddit.com/r/excel/comments/...orksheets/

well to get this done you will have the highest success rate if you access not just one help opportunity but more. And thats why I wrote my first post. Its Strg C Strg V, but I don't think it matters if it is my first or last post cause the problem is the same and thats what forums are for.

Kind regards

Hi,

Please read these rules: http://www.clever-excel-forum.de/misc.php?action=help&hid=10

(07.08.2017, 19:00)schauan schrieb: [ -> ]Hello Patrick,

I think, you want to execute p.E

Code:

Range("B5").Select

from another sheet?

you can write

Code:

...
With Sheets("MySheetAnywhere")
    .Range("B5").ActiveCell.FormulaR1C1 = _
   ...
   .Range("B5:B6").AutoFill Destination:=Range("B5:B26"), Type:=xlFillDefault
    ...
    With .Range("B3").Interior
      ...
   End With
   ... 
End With
...

Important is the Point before every Range or Cells you want to use with With. If you interleave ? With, the "pointed range code"  is in relationship to the last With.

Thank you for answering to my post:) I tried your method but it's not working... do you want me to replace all range statements at the front of a line with .range or just the one you pointed out?:) and whats up with the 

   destination:=Range("B5:B26"), 

do I need to change something there with the range?

Kind regards

Hello Patrick,

the Points are used only for the Ranges between With and End With only for Sheets("MySheetAnywhere").
If you have to do something on another sheet, write the name and the range p.e. destination:=Sheets("MyOtherSheet").Range("B5:B26") or without Name (and without point) p.e. destination:=Range("B5:B26") , if you are sure, that this is the active sheet.

Der Thread gelfällt mir

Zitat:Firstly, this is NOT your first post, it is your fourth.

Secondly, your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Zitat:and thirdly, the post does not comply with Rule 8 - cross-posting without a link to the other forums ist not fine. https://www.reddit.com/r/excel/comme...nt_worksheets/ and http://www.clever-excel-forum.de/Thr...ent-worksheets

Zitat:Hellownero - please do not start another thread on these forums until you have read this forum's rules.

:19: