Posts by WFischer

Re: Userform Navigation

Ok I will have a look at that then, thanks.

Re: Userform Navigation

ok thanks, that helps already.

But what if I have another button on userform 3 that points to the Main menu? This code will always show userform 1 or 2 then as well. How to prevent that?

Sub button_click() 
    Me.Hide 
    userform3.Show 
    me.show
 End Sub

Re: Calculating hours "in" and hours "out" two different rates

How about this?

A lot of case distinctions, I know

Sub test()




Dim js As Double 'Job Start
Dim je As Double 'Job End
Dim ih As Double 'in hours
Dim oh As Double 'out hours
Dim duration As Double




duration = (je - js) - 24 * Int((je - js) / 24)




If js <= 8 And (je >= 18 Or je <= js) Then
    ih = 10
ElseIf je <= 18 Then
    ih = WorksheetFunction.Max(0, je - 8)
End If




If js >= 8 And js <= 18 Then
    Select Case je
        Case Is >= 18, Is <= 8
            ih = 18 - js
        Case Is > js
            ih = je - js
        Case Else
            ih = 10 + je - js
    End Select
End If




If js >= 18 Then
    Select Case je
        Case Is >= js, Is <= 8
            ih = 0
        Case 8 To 18
            ih = je - 8
        Case Else
            ih = 10
    End Select
End If




oh = duration - ih








End Sub

edit: you have to convert time to nueric, e.g. 0830 -> 8.5, but that should be easy

Hi guys,

I want to achieve the following: Say, I have 3 Userforms and i want to be able to access Userform 3 via buttons in Userform 1 and 2.
Userform 3 has a "back" button that redirects me to the previous userform.

Is there an elegant way to do this?

userform1:

sub button_click


unload me
userform3.show


end sub

userform2:

sub button_click


unload me
userform3.show


end sub

userform3: Pseudocode sth like

sub button_click


unload me


if 'Userform1 was previously active then
userform1.show
endif


if 'Userform2 was previously active then
userform2.show
endif


end sub

Re: loop through all sheets and sort by cell colour

Hi Roy, I see your point now, thanks for explaining.

Also, thanks Robert and esp. Smallman for your insights.

Re: loop through all sheets and sort by cell colour

Hi Roy,

thanks for your reply. The only errors I get are occur when there are no red cells in my column, so that is fine.
Also, I don't want to use "usedRange" since it is not updated properly. Sometimes it is larger than the actual used range, so this shifts some rows way too fat to the bottom.

However, I kind of worked around the problem meanwhile, but I do not quite understand it:

mysheet.activate

at the beginning of the code solves it, but i have no idea why it is necessary. Somebody knows?

Thanks,
Willi

Hi guys,

I want to loop through all sheets and sort by color, so that red cells come to the bottom. I loop through every column because red cells can be in every column.
This is my code. It works fine, but only works in the activesheet and doesnt loop through sheets.

Any ideas?

Thanks!

Sub Hauptmenü_Speichern_Click()




Dim i As Long
Dim mySheet As Worksheet




'loop through sheets
For Each mySheet In ActiveWorkbook.Worksheets




    For i = 1 To mySheet.Cells(1, Columns.Count).End(xlToLeft).Column 'count columns in sheet








            'turn errors off (you would get errors if there are no red cells in the column)
            On Error Resume Next




                mySheet.Sort.SortFields.Clear
                mySheet.Sort.SortFields.Add(Range(Cells(2, i), Cells(mySheet.Cells(Rows.Count, 1).End(xlUp).Row, i)), _
                        xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)




                    With mySheet.Sort
                        .SetRange mySheet.Range(Cells(1, 1), Cells(mySheet.Cells(Rows.Count, 1).End(xlUp).Row, mySheet.Cells(1, Columns.Count).End(xlToLeft).Column))
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With




            'turn errors on again
            On Error GoTo 0




        End If




    Next i
Next mySheet




'ThisWorkbook.Save




End Sub

Re: Insert graphic in Word document header via Excel VBA

Hey,
I get
[h=1]
Run-time error '5941' the requested member of the collection does not exist[/h]
in the last line of your code. But I think, I can modify the picture in Excel.

Thanks! Can I somehow flag this as resolved in this forum?

Re: Insert graphic in Word document header via Excel VBA

Thanks for your answer,

it works

CAn you also Tell me how to change size and position of the graphic then?

Thanks

Hi guys,

I have this problem:

I create a Word dosument with Excel VBA. Now I want to insert a graphic into the header. Since several people have to use the Excel workbook, the graphic is not saved in a separate file. Instead i have put in in an extra sheet in my workbook.

How can I put this graphic to the Word document header (and change size and position)?

To avoid misunderstandings: I do not use Word VBA entirely, only Excel VBa is used.

I have experimented with the macro recorder, but something like this does not work:

Dim objHeader As Object   


Worksheets("Logo").Shapes.Range(Array("Group 4")).Copy


Set objHeader = wdApp.ActiveDocument.Sections(1).Headers(1)
objHeader.pasteandformat

I have psoted this already here (German)
http://www.herber.de/forum/arc…1336/t1335594.htm#1335594

I hope for your help

Thanks,
Willi