Archive

Posts Tagged ‘MS Access’

Custom InputBox for VBA, MS Access, Excel

May 13, 2018 Leave a comment

Since the modern dialog box, we’ve thought why not make modern InputBox? Oh, this time there are tones of examples on the web about custom InputBox for VBA. So what’s the point?

Well the convenience is you have a component collection and you can have variety of components in one place. besides, most custom inputboxes on the internet look cool but to be true modal, they will secretly use a infinite loop with DoEvents in the background. Tbh, they only look cool. Functionally same old inputbox.

We need a cool inbox. Functionally and graphically somewhat advanced than the others. What do we mean by functionally advanced? The default InputBox does not perform any validations. Any user entry is returned as string. Which means, you have to let the user enter the incorrect [type of] value before checking whether the value is allowed or not. This was default in 19xx but we are in 2018. User should only be allowed to enter correct types of values. i.e. only be able to enter numbers when asked for numbers, or only valid emails when asked for emails and so on.

Here is a screenshot which asks for a text. (multi line)

Modern-Multiline-inputbox-for-vba

When a multiline inputbox is requested, the size of the input box automatically increases.

 

obviously we should be able to change the theme colour. like below. Only a valid date can be entered. if Email is requested, a valid email is required before closing. etc. etc.

Modern Inputbox for vba purple

 

if password is requested, a masked field is shown, if shortdate is requested,

Modern Input box for VBA

 

Check my GitHub and download the sample ACCDB or EXCEL and see how you can copy this function to your project.

If you already have the vba_tools.dll linked in your project. below code will make it work for you.

Private Sub Command78_Click()
‘    Public Enum InputBoxType
‘    {
‘        Password        = 1,
‘        Text            = 2,
‘        MultilineText   = 32,
‘        Number          = 4,
‘        ShortDate       = 8,
‘        LongDate        = 16,
‘        DateTime        = 48,
‘        Email           = 96,


‘    }
    gDll.Toast "you’ve entered: " & gDll.DLL.showinputbox(Type:=2, Title:="", Message:="You can change my text and colour", ThemeBg:="#aa66cc", ThemeForeColour:="")
End Sub

or simply

result = gDll.DLL.showinputbox(Type:=2)

As mentioned, check out my GitHub and share your thoughts in the comment box.

Advertisements

Cool modern DialogBox for MS Access / VBA applications.


Hello there, again it’s about VBA and dll Smile

 

This idea just popped out after someone asked a question regarding my previous non-blocking notification. This time why not use a blocking notification?. Or do we say a dialog box?

So what’s special? Well, standard message boxes are great but sometimes you want little more than “just plain” standard. I.e

  • Be able to have some colours
  • Be able to have more than 3 buttons “i guess i always wanted this”
  • Be able auto-close
  • Be able to use HTML tags
  • not stressing your vba app with a loop?

Meet the new simplified DialogBox for VBA users. This dialogbox will allow above listed features and should help you to keep your application colourful. 🙂 This feature is still under development and could some feedback from testers.

check out my Github for samples. There is a sample ACCDB file with some custom class and a vba wrapper around the DLL.  Let me know what you think.

some screenshots

4Buttons

Modern DialogBox Red

DialogboxGreen

Modern DialogBox Green

Download the free dll from my GitHub page. A beautiful dialogbox can be created with simple as this code.

'usign the wrapper it would be as simple as 
  Debug.Print gDll.DialogRich("This is a title", "Some content", (vbExclamation + vbYesNo))

Non-blocking notifications/ Toaster Popups for Microsoft Access (VBA)

August 26, 2016 5 comments

Hello there!! Yes! Been another long time from my last post. Absolutely wonderful how much support, contacts, messages, critics & love I get. Thank you all and you guys are amazing. Smile SmileDue to the nature of my profession, I don’t quite get time to keep up with my blogs now. But I try to get some time for new posts. Like right now I’m writing this blog while crossing the English Channel. Yes off to Paris for a weekend.. Smile Smile

I thought this is worth sharing as there are plenty of developers still working on MS Access who wants some nice UI functions. So here it is!.

Billy, –my new online friend- who works on a Microsoft Access project, asked (wished himself) if there is a way to replicate “Toastr” pop-ups for Microsoft Access (VBA).

Similar to Toastr, popups should allow customisation. Such as the position, animation, background color, dragable, transparency etc.

Mainly to show non-blocking messages. Something that user should know but not necessarily need to interact with it.

To achieve this in VBA, a borderless form and few windows APIs can be used.

1. An API to place the form where we want (ideally somewhere near to where the user is focusing. In case of multiple screens)

2. To make the form transparent

3. Fade in/out (some animation) to mimic toasting effect.

4. Dragable. In case if the toast is blocking users view and user wants to move it somewhere.

 

The end result will look somewhat like this. (Written in pure VBA using some APIs)

nomal_toast[3]

Billy was happy but NOT “happy happy” with this result. Since MS office is single threaded, fading-in, fading-out or any loop operation will eat the entire applications processing cycle. Billy didn’t like the fact that his application was waiting when showing multiple toasts.(VBA really struggled when 10 or more toasts were cooked simultaneously. (Don’t ask me why on earth he wanted to show 10 toasts at a time)

In essence, this was not a “non-blocking” notifications.

He said,

No wonder, why I couldn’t find anything similar online. It’s not worth doing in VBA because of the single thread limitation”

Oh well! no one done it yet? sounds like a challenge to me! Smile

I thought, what if you could

Write your own .NET DLL?

Surely this should save (single threaded) host application’s processing cycle?

A quick search on the internet gave me various results how to access .NET DLLs from VBA. one in particular(1,2) was very interesting. Which wraps C# DLLs in a way that VBA could read it. I quickly followed Robert Giesecke template and re-wrote the VBA toasting script within C# class.

Here comes the technical part:

You can consume .dlls in VBA in two different way. Either the .dll is already available in GAC (com visible / registered .Dlls) or load the .dll dynamically.

I didn’t like the fact that your .dll have to be registered. Clients might or might not want to install/register a .dll. So, I went with loading dynamically option.

To load a .dll dynamically in VBA, you need to prepare something.

1.Register/Declare the Windows API calls to load & unload DLLs

2.Wrap all your dll functions within a class and pass it to the caller as object.

 

Loading and unloading would be: (add ptrSafe accordingly)

Private Declare Function FreeLibrary Lib “kernel32” (ByVal hLibModule As Long) As Long
Private Declare Function LoadLibrary Lib “kernel32” Alias “LoadLibraryA” (ByVal lpLibFileName As String) As Long

 

I have wrapped all our functions within a class (KRISH_VBA_TOOLS). We also need to register this class within VBA so we could consume it like any other classes.

I put the .dll in my app folder hence I don’t need the absolute path name.

#IF VBA7

Public Declare PtrSafe Function KRISH_VBA_TOOLS Lib “VBA_TOOLS.dll” () As Object

#ELSE

Public Declare Function KRISH_VBA_TOOLS Lib “VBA_TOOLS.dll” () As Object

#END if

NOTE: MS ACCESS VBA will expect you to provide absolute path for any .dlls that you load. In our case, we are going to load the .dll manually and leave the abstract declaration pretending that we provided the dll path. (I needed this way because I use option Explicit in module level)

Loading the vba_tools.dll and toasting some notifications would be.

Public Function FN_TOAST_DLL(iMessage As String, Optional iCLOSE_DURATION As Long = 3000, Optional iType As String = “success”, Optional iANIME_DURATION As Long = 1000, Optional iFONT_COLOR As String = “#FFFFFF”, Optional iX As Long = 0, Optional iY As Long = 0, Optional iANIME_DIRECTION As Integer = 1, Optional iPARENT_HWND As Long = 0)

On Error GoTo LABEL_EXIT_ROUTINE:

If gTOASTER Is Nothing Then
LoadLibrary (FN_APP_GET_BASE_PATH & “CCMSDLL.dll”)
Set gTOASTER = KRISH_VBA_TOOLS()
GoTo LABEL_TOAST
Else
GoTo LABEL_TOAST
End If

LABEL_EXIT_ROUTINE:
On Error GoTo 0
Exit Function

LABEL_TOAST:

Select Case iType
Case “error”
iType = “#F76160”
Case “success”
iType = “#26ad82”
Case Else
iType = “#26ad82”
End Select

Dim mRect As RECT
If iPARENT_HWND <= 0 Then
If iX = 0 And iY = 0 Then
GetWindowRect Application.hWndAccessApp, mRect

iANIME_DIRECTION = 0
End If
Else
GetWindowRect iPARENT_HWND, mRect
End If

‘set up the position
iX = mRect.Left + 360
iY = mRect.Top + 1

On Error Resume Next
gTOASTER.FN_SHOW_TOAST iMessage, iCLOSE_DURATION, iType, iANIME_DURATION, iFONT_COLOR, iX, iY, iANIME_DIRECTION

End Function

Read more…

%d bloggers like this: