Sort a Range in a Hidden sheet

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
Hi there
1)Is ther a code to make a userform appear from top reight/left corner in a wavy way ?

2)The sheet that contain the range needs to be sorted must be activated .
I nedd to make my sort without activating the sheet that conatain the range, cuz this sheet is very hidden and I want it to contiue to be hidden.
Is there a soultion to sort my range in the hidden sheet without activating this sheet ?
Here is my try
"
Code:
Worksheets(""Sheet1"").Range(""B:B"").Sort Key1:=Range(""B1""), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
"
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, helmekki,

this works for me
you must set the "KEY" to the same worksheet !!
(no double quotes !)

Worksheets("Sheet1").Visible = xlVeryHidden
Worksheets("Sheet1").Range("B:B").Sort Key1:=Worksheets("Sheet1").Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
I prefer this syntax
Code:
With Worksheets("Sheet1")
.Range("B:B").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
when changing the worksheet you wil only need to do this once (spo reducing typos)

can you explain a bit what your first question means
perhaps this:
userform appears topright
moves down and to the left
again to the right
to the left
so on
stops somewhere on your sheet ...


kind regards,
Erik
 

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
Thank u very much erik.van.geit (y) (y)

i mean the userform should appear as forms appear in the powerpoint app

i.e. userform appears small in top left corner and moves to the center slowly and becomes larger and larger

Is there a code to do this with userform in Excel App ?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
try this
Code:
Option Explicit
'Erik Van Geit
'051123

    Dim UW As Integer
    Dim UH As Integer
    Dim UL As Integer
    Dim UT As Integer
    Dim AW As Integer
    Dim AH As Integer
    
Private Sub UserForm_activate()

    Dim GOAL As Integer
    Dim I As Single

    With Me

    GOAL = (AW - UW) / 2

        For I = -UL / 2 To GOAL Step 0.2
        .Width = UW / GOAL * Application.Max(I, 0)
        .Height = UH / UW * .Width
        .Left = UL / GOAL * I
        .Top = UT / GOAL * I
        DoEvents
        Next I

    End With
    
End Sub

Private Sub UserForm_Initialize()
AW = Application.Width
AH = Application.Height
    With Me
    UW = .Width
    UH = .Height
    UL = (AW - UW) / 2
    UT = (AH - UH) / 2
    .StartUpPosition = 0
    .Top = -9999
    End With
    Load Me
End Sub
 

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
yes, this is it.....................if you do have some other ways of special effects regrading userform.....

please do post/emil them to me , i would highly appreaciate it ?

(y)
 

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
Somthing like flashing or sound inclosed with userform.....etc
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
sound is not my speciality in Excel
(on the piano it is :) )
I know there are some examples on the board
and I believe Ivan F Moala has some stuff on his site (no sure)

flashy
experiment with this


Code:
Option Explicit
'Erik Van Geit
'051123

    Dim UW As Integer
    Dim UH As Integer
    Dim UL As Integer
    Dim UT As Integer
    Dim AW As Integer
    Dim AH As Integer
    
Private Sub UserForm_activate()

    Dim GOAL As Integer
    Dim I As Single
    Dim j As Integer

    With Me

    GOAL = (AW - UW) / 2

        For I = -UL / 2 To GOAL Step 0.2
        .Width = UW / GOAL * Application.Max(I, 0)
        .Height = UH / UW * .Width
        .Left = UL / GOAL * I
        .Top = UT / GOAL * I
        DoEvents
            j = j + 1
            If j Mod Int(GOAL) = 0 Then
            j = 0
            .BackColor = IIf(.BackColor = &H80000001, &HFF&, &H80000001)
            End If
        Next I
    .BackColor = &H80000001
    End With
    
End Sub

Private Sub UserForm_Initialize()
AW = Application.Width
AH = Application.Height
    With Me
    UW = .Width
    UH = .Height
    UL = (AW - UW) / 2
    UT = (AH - UH) / 2
    .StartUpPosition = 0
    .Top = -9999
    .BackColor = &H80000001
    End With
    Load Me
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Code:
Dim msg As String
Dim starttime As Double
Const delay As Double = 0.1
msg = "GREAT NEWS: You are viewing a growing message!"
    With Label1
    For I = 1 To Len(msg) Step 1
    .Caption = Left(msg, I)
    starttime = Timer
    DoEvents
    Do
    Loop While Timer - starttime < delay
    Next I
End With
 

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
Allow me to express my deep gratitute for your contiuoes help and time given to me.........................erik.van.geit

(y)(y)(y) **
 

Forum statistics

Threads
1,078,398
Messages
5,339,983
Members
399,345
Latest member
elvy

Some videos you may like

This Week's Hot Topics

Top