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
"
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Allow me to express my deep gratitute for your contiuoes help and time given to me.........................erik.van.geit

(y)(y)(y) **
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top