true power of MS excel. update: PowerBI

[kirby21]

see 2nd reply for powerbi update​

Hello guys ginawa ko lang tong thread para sa mga kasymb natin na need ng tulong about MS excel. mga reporting staff, encoders, data analyst, secrectary, or Ms excel systems. pasok po kayo dito. share tayo ng knowledge. i will provide tutorials too. believe me after learning this things after a month i got my promotion to go up another level from our company. so try it now. made your bosses like their jaws drop.​

on my first example. you will see chart with controls like the picture below.
View attachment 156421

here you will see conditional formatting with toggle.like the picture below.
View attachment 156426

now you will see a picture album using excel. but in this tutorial i'll give you an example of animal album.
View attachment 156660

in this picture you will see a chart that looks like a temperature. it would be helpful to a presentations with a sales and target sales.
View attachment 156997

now in this picture you will see a speedo meter chart with control. the tutorial was provided from the attachment.
View attachment 157003

at eto naman ang sample dashboards ko

sample 1

sample 2

​

if nagustohan nyo po. pwede nyo sya idownload sa baba.

digital clock by dhok si...

hi mga tol share ko lng nala2man ko sa excel 2010..ngaun araw na to tu2ro ko sa inyo kung paano gumawa ng digital clock sa excel 2010
e2 un code sa vba
Dim running As Boolean

Sub test()

Const QRT As Integer = 4, Year As Integer = 12

Cells(1, 1) = 12 / QRT
Cells(1, 2) = 12 / Year

End Sub

Sub test2()

Dim rng As Range
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lr, 1))
rng.Font.ColorIndex = 4

End Sub

Sub Clock()

running = Not (running)
Do While running = True
DoEvents
Range("A1") = Now
Loop
End Sub​

- - - Updated - - -

kopyahin nyo lng yan..tapos sundin nyo un video na ginawa ko
e2 un link http://www.youtube.com/watch?v=csXhTkHVp08

- - - Updated - - -

e2 pa isang ginawa ko hijri to gregorian calendar
http://www.youtube.com/watch?v=8uUrpmDtLow

sana po magshare din ang iba,


grading system excel application upload by our contributors. http://www.symbianize.com/showthread.php?t=1132342&p=19858679&viewfull=1#post19858679

eto na po para yung iba di na masyado magtanong sa excel tricks. lalo na mga charts with controls. im giving you all this.

Excel hacks.rar


eto po splitted into two parts. thanks to devildarkrs
part 1
http://www.symbianize.com/attachment.php?attachmentid=1043979&d=1436253924
part 2
http://www.symbianize.com/attachment.php?attachmentid=1043978&d=1436253924
credits to creator of the book.

HOW TO UNLOCK VBA PROJECT
credits goes to athenesia​

try mu to...
1. Open mu yung file na may protected vba project.
2. Pag naka open na, Ctrl+N ka para mag open ng new blank workbook.
3. Pag open na yung new blank workbook, Alt+F11 ka para mapunta sa vba projects.
4. Under dun sa vba ng new blank workbook(Book1) isert ka ng module.
5. Module 1 yan default name. Ipaste mu itong code na to.

Option Explicit

Private Const PAGE_EXECUTE_READWRITE = &H40

Private Declare PtrSafe Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As LongPtr, Source As LongPtr, ByVal Length As LongPtr)

Private Declare PtrSafe Function VirtualProtect Lib "kernel32" (lpAddress As LongPtr, _
ByVal dwSize As LongPtr, ByVal flNewProtect As LongPtr, lpflOldProtect As LongPtr) As LongPtr

Private Declare PtrSafe Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As LongPtr

Private Declare PtrSafe Function GetProcAddress Lib "kernel32" (ByVal hModule As LongPtr, _
ByVal lpProcName As String) As LongPtr

Private Declare PtrSafe Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As LongPtr, _
ByVal pTemplateName As LongPtr, ByVal hWndParent As LongPtr, _
ByVal lpDialogFunc As LongPtr, ByVal dwInitParam As LongPtr) As Integer

Dim HookBytes(0 To 5) As Byte
Dim OriginBytes(0 To 5) As Byte
Dim pFunc As LongPtr
Dim Flag As Boolean

Private Function GetPtr(ByVal Value As LongPtr) As LongPtr
    GetPtr = Value
End Function

Public Sub RecoverBytes()
    If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6
End Sub

Public Function Hook() As Boolean
    Dim TmpBytes(0 To 5) As Byte
    Dim p As LongPtr
    Dim OriginProtect As LongPtr

    Hook = False

    pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA")


    If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then

        MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6
        If TmpBytes(0) <> &H68 Then

            MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6

            p = GetPtr(AddressOf MyDialogBoxParam)

            HookBytes(0) = &H68
            MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4
            HookBytes(5) = &HC3

            MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6
            Flag = True
            Hook = True
        End If
    End If
End Function

Private Function MyDialogBoxParam(ByVal hInstance As LongPtr, _
ByVal pTemplateName As LongPtr, ByVal hWndParent As LongPtr, _
ByVal lpDialogFunc As LongPtr, ByVal dwInitParam As LongPtr) As Integer

    If pTemplateName = 4070 Then
        MyDialogBoxParam = 1
    Else
        RecoverBytes
        MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _
                   hWndParent, lpDialogFunc, dwInitParam)
        Hook
    End If
End Function

6. Insert ka ulit isa pang module(Module 2) at ipaste naman tong code na to.

Sub unprotected()
    If Hook Then
        MsgBox "VBA Project is unprotected!", vbInformation, "*****"
    End If
End Sub

7. irun mu na ngayon yung code sa module 2.
unlock na vba project mu.

 

[URAZKY]

Re: MS excel reportings, presentations, and vba helping thre

sir, paturo na naman sa graph & control mo. gusto ko matuto niyan pano mo ginwa yan?

 

[kirby21]

Re: MS excel reportings, presentations, and vba helping thre

Introduction to PowerBI​

View attachment 348762

View attachment 348763

View attachment 348764

View attachment 348765

View attachment 348766

View attachment 348767

will provide tutorials soon.

 

[bmf24]

Re: MS excel reportings, presentations, and vba helping thre

Sir maraming maraming salamat sa thread na to...looking forward sanext post mo...magagamit ko ito sa work.

 

[URAZKY]

Re: MS excel reportings, presentations, and vba helping thre

sir, ms office 2007 paturo naman jan sa graph very useful

 

[kirby21]

Re: MS excel reportings, presentations, and vba helping thre

sir, ms office 2007 paturo naman jan sa graph very useful

uu naman. nagprovide na ko sa attachment ko ng tutorial video. di ko madisplay anlaki eh.

 

[URAZKY]

Re: MS excel reportings, presentations, and vba helping thre

i mean, sir. panu mo po nagagawa for example yung sa chart with control.

 

[kirby21]

Re: MS excel reportings, presentations, and vba helping thre

i mean, sir. panu mo po nagagawa for example yung sa chart with control.

oo nga nagattach na din ako ng tutorial kung pano gawin sa attachment.

 

[URAZKY]

Re: MS excel reportings, presentations, and vba helping thre

sir, other tus po. thanks

 

[kirby21]

Re: MS excel reportings, presentations, and vba helping thre

maya po konte magdagdag ako. nasa office pa kasi ako eh. ano po ba work mo?

 

[ackidcat]

Re: MS excel reportings, presentations, and vba helping thre

sir.. ask ko lang may code po ba kayo para sa macro na auto add ng current date/time? example pag pinindot ko yung hotkey nya automatically mag a-add sya sa current cell ng date/time today.. ganun.. salamat in advance..

 

[muthym]

Re: MS excel reportings, presentations, and vba helping thre

sir.. ask ko lang may code po ba kayo para sa macro na auto add ng current date/time? example pag pinindot ko yung hotkey nya automatically mag a-add sya sa current cell ng date/time today.. ganun.. salamat in advance..

Need mo pa ba talaga code? Kung pindot lang naman, pindutin mo na lang CTRL + ; or CTRL + : to display date/time.

Since nasa programming section tayo, you can try the following codes:

a)
Range("A1").Value=Date
Range("B1").Value=Time
Range("C1").Value=Now

b)
Cells(1,1).Value=Date
Cells(1,2).Value=Time
Cells(1,3).Value=Now

c)
ActiveCell.Formula="=Today()"

ActiveCell.Offset(0,1).Select
ActiveCell.Formula = "=Now()"
Selection.NumberFormat = "h:mm AM/PM"

 

[kirby21]

Re: MS excel reportings, presentations, and vba helping thre

Need mo pa ba talaga code? Kung pindot lang naman, pindutin mo na lang CTRL + ; or CTRL + : to display date/time.

Since nasa programming section tayo, you can try the following codes:

a)
Range("A1").Value=Date
Range("B1").Value=Time
Range("C1").Value=Now

b)
Cells(1,1).Value=Date
Cells(1,2).Value=Time
Cells(1,3).Value=Now

c)
ActiveCell.Formula="=Today()"

ActiveCell.Offset(0,1).Select
ActiveCell.Formula = "=Now()"
Selection.NumberFormat = "h:mm AM/PM"

tama. yung today pwede rin now(), . salamat sa pagsagot ha, patulong sa thread ah. share ka din knowledge mo. alam ko madami ka din alam sa excel eh.

 

[rmslive]

Re: MS excel reportings, presentations, and vba helping thre

ako boss may tanong, pano naman po gagawin ko if ever need ko mag display ng isang records sa ibang sheet automatically, tapos removed nadin po from pinag kuhanan. but then sana din po, every update ko ng pinag kukunan na sheet na na-pupunta padin po sa ibang sheet na gusto ko kapuntahan,

 

[kirby21]

Re: MS excel reportings, presentations, and vba helping thre

ako boss may tanong, pano naman po gagawin ko if ever need ko mag display ng isang records sa ibang sheet automatically, tapos removed nadin po from pinag kuhanan. but then sana din po, every update ko ng pinag kukunan na sheet na na-pupunta padin po sa ibang sheet na gusto ko kapuntahan,

yung katulad ng binigay ko sayo na report?
kung hindi, well you need pivot table and refresh it everytime na gusto mo makita yung update. kung ayaw mo magrefresh do it using macro.
pwede din walang macro but you need a lot of formulas.

including vlookup, offset, index etc etc. mga reference formulas.

 

[leiner07]

Re: MS excel reportings, presentations, and vba helping thre

pa BM aq TS..laking tulong nito keep it UP!

 

[dannyps2]

Re: MS excel reportings, presentations, and vba helping thre

Subscribe ako dito sir... salamat sa thread mo im sure marami kami na ngangalangan nito.

 

[muthym]

Re: MS excel reportings, presentations, and vba helping thre

tama. yung today pwede rin now(), . salamat sa pagsagot ha, patulong sa thread ah. share ka din knowledge mo. alam ko madami ka din alam sa excel eh.

i-clarrify ko lang. Today() displays date *ONLY*. Now() displays date & TIME. Using Now(), if formatted as DATE - date will be displayed, if formatted as TIME, time will be displayed as shown in my letter C example.

@kirby21
No problem

 

[rmslive]

Re: MS excel reportings, presentations, and vba helping thre

yes sir, same padin po ung pinag uusapan natin na file. di po ba pwede na kagaya ng sa masterlist encoding un na pag merong cancelled mapunta siya sa isang sheet, na lahat ng cancelled nandun.

 

[vhanessxxx]

Re: MS excel reportings, presentations, and vba helping thre

sir thank you.. kailangan ko to pahingi naman ng tutorial na nakalagay lang sa ms word masyadong mabilis di ko masundan..