Wednesday, May 17, 2017

Selecting a worksheet from within a userform in Excel VBA

I came across this problem recently.

I had a 'Goto to sheet' button in the main sheet of an Excel workbook that contained a large number of sheets.

Pressing that button caused a userform to popup, showing a list of all the relevant sheets. This is the code behind that button

    With UserForm1
        .StartUpPosition = 0
        .Show vbModeless
    End With


The user would select a sheet from the listbox and press Ok, which would activate the selected sheet, using this code.

    Dim s As String
    s = ListBox1.Value

    Worksheets(s).Activate
    Worksheets(s).Range("B1").Activate
    Unload Me


All appeared to be working well, however, it seems that Excel showed the correct page but, internally, it was pointing to the main sheet.

(The main sheet had named A14 as 'emailAddr'. 
Clicking on A14 in the main sheet would show 'emailAddr' at the address cell, which is the cell above A1 - the one that shows the cell names...)

When VBA switched to the selected sheet, if I clicked in A14, it would show 'emailAddr' in the address cell instead of A14.

If I then manually went the previous sheet and then back to the one I wanted, it'd all be ok.


When I changed the 
    .Show 
to 
    .Show vbModeless

everything worked as it should!



ps: Comments and/or links to this article are most welcome!

Saturday, December 10, 2016

Very useful interactive CSS reference

I came across this very handy, interactive, visual CSS reference guide.

It describes all CSS properties and shows how they would appear on a page.

And, while studying the above, you may want to enjoy some cool music.


ps: Comments and/or links to this article are most welcome!

Saturday, October 8, 2016

Visual Studio 2015 installation issues ('package did not load ...')

[updated 9oct2016]

I am using VS2015 Community edition and recently decided to apply some updates that VS suggested, as well as add a few more addins.

Once the installation finished, I tried to open the solution I had just been working on, only to receive an error from VS about 'Roaming package did not load'.

I rerun the VS2015 installer and, after a length wait, was told that all was well.
I run VS and received the same error.

I then uninstalled VS (which took quite some time) and then re-installed it, only to have the same error appear.

I tried tricks from the web, like deleting the 
C:\Users\%user%\AppData\Local\Microsoft\VisualStudio\14.0\ComponentModelCache directory, to no effect.

Nothing I tried worked.

I even tried an uninstall with the '-uninstall -force' option, which ended up coming back with an error about 'bad blocks' (can't recall exact message).

All in all, nothing worked.

I then resorted to using the 'Total Uninstaller' tool from MS which removes all previous versions on VS.

After a reboot, I proceeded with a complete re-install of VS2015.

It took its good time to do the blasted install and, not surprisingly, it came up with an error at the end, regarding a problem with VC++ runtimes.

However, It did not say that the install failed, rather that it could not install those runtimes.

It turns out that something that I installed, installed VC++ redistributable v14.0.24215, whereas VS2015 wants v14.0.24210 (32 and 64-bit)

For me in any case, removing my (newer!) version of those runtimes, fixed the problem

I hope this works for you too.


[update:
Important note: Running TotalUninstaller actually made the re-install work better! It became clear that, prior to running this, there were leftovers from previous VS installations that caused this installation to make incorrect assumptions!]

The steps were:
- Unistall VS2015 via vs_community /uninstall
- run TotalUninstaller (Setup.ForcedUninstall.exe)
- reboot (it is windows after all...)
- run VS2015 installer. It now looks like it should! It does a proper, clear installation.



ps: Comments and/or links to this article are most welcome!

Sunday, June 26, 2016

open source software I use, part 2

[update 26-jun-2016
Thanks to Audrina W., I have now corrected the link to Smarty

Note that a lot of what is shown here was relevant in 2010 or so but is now out of date.]


Continuing on from part 1 of this series, here are some more open-source apps I use:


- jQuery. I have found this javascript library to be indispensable! It is a simple, non-imposing, yet deceptively powerful library. I will not write why or how it is so good here; there are lots and lots of articles on the web doing just that. I'd strongly suggest you have a look at it, if you do any kind of javascript work. It is not the only library out there nor the most all-encompassing but, to me, it is one of the most useful and fun to work with!


Web development:
I will bundle all the tools I use in one and see how it goes! Most of them do not need any explanation (but feel free to tell me otherwise!)


PHP: Server-side scripting language.
Reasonably
popular. I believe there are a couple of apps written in it.
(cough cough! A couple of million apps maybe! I read somewhere that there are approx 50 million web-sites running on PHP!!!)


btw, if you are a strongly-typed language developer (say, C, C++, C#, C+1, D-1 whatever, Pascal etc), do remember that this is a scripting language!
It allows you to write apps, large and small, very quickly, with nothing more needed than a text editor.
However, it also allows you to create apps that suck and are full of bugs.
Since it has no type-checking nor any compile-time smarts, you are always running the risk of bugs in your source.
A small mis-type of a variable name could bring down a large app, long after you've moved onto greener pastures.


That said, see my point above about 50 million web sites. (Not that more is better!)


Smarty: Template engine for PHP. Allows separation of presentation logic from PHP code
(makes using MVC just that much easier)


XDebug: Debugger for PHP. Works well with Eclipse. Must-have


EclipsePDT: The mother of all IDEs. Initially written by IBM, later offer to the OSS community. Thank you Big Blue!
(The PDT version is tailored to PHP development)




SAJAX: This library will help you write ajax-enabled apps; it provides all that is needed to make calls from javascript to PHP and back, passing arguments to and fro.
I have found it very useful, however I am now using the jQuery ajax support for most of this work. It is not the same but achieves the same result.
(NOTE: Jul 2010: jQuery provides all that is needed to do AJAX easily, so there is no need for this)


Mantis: Bug-tracker, written in PHP.


XAMPP: If you need a LAMPP stack (Linux Apache MySQL PHP Perl) or a WAMPP (Windoze....) then your best shot at a simple, no-hassles installation is via the XAMPP installer.
Download and run it and, presto, your machine has all the essentials for web development.
[update 2015]I now prefer WAMP. One of the great things about it is that it allows you to easily switch between different versions of PHP, Mysql and Apache, all via a menu entry.


PHPMyAdmin: MySQL database administration tool. Simply extract into a web dir onto your LAMPP server, point your browser at it and there you are! You now have a web-based, browser-driven MySql administrator!


JasperReports: OSS's answer to Crystal Reports.
This is just the reporting engine and runs off XML report-definition files. Do not panic, there is a GUI designer Eclipse plugin for it that makes report design a breeze (screenshot). There is also a stand-alone GUI for it, called iReport.


Note: Eclipse also has a reporting tool, called BIRT. I haven't used it a lot, however you should have a look at it too.




And now, for some non-development-related OSS tools:
- KeePass: Password vault. There is also a PocketPC version of it. It has a smart 'find out what form I am in and paste out the correct credentials' way of doing things.


I use it to store all my passwords. Before I leave work, I export the password to KeePass's native format, upload it to gmail, go home and download from gmail. I then get KeePass to import it and update existing keys and add new ones. I then export the merged results, upload to gmail, go to work, download from gmail and repeat all over again.


Webmin: A web-based, browser-driven Linux management tool. Allows you to manage almost everything on your Linux box from just a browser. You can start/stop jobs, administer users, administer server apps (such as Apache, MySql, ProFTPD, Samba etc etc).
It pretty much does everything. All you need to do is extract the webmin source (PHP) files onto your web server, point your browser to https://myserver:10000 and you are in control!


If you do end up using any of the above, let their authors know; they'd appreciate acknowledgement.
more later...


ps: Comments and/or links to this article are most welcome!

Wednesday, May 11, 2016

Uninstall remnants of Visual Studio 13 and above

Microsoft has created a utility that is run from the command line (administrator level needed), which cleans up any leftover files or registry keys that may be left over after a failed un-install.

This can come handy when a VS uninstall fails. If you try to install VS again, you may find that it detects the partial, previous installation and misbehaves.
In my case, it kept insisting that the installation dir was on the D: drive and would not let me change it.


You can find it here.


ps: Comments and/or links to this article are most welcome!

Tuesday, February 2, 2016

I've published my first music album

All work and no play... 

Well, I decided to have a little fun (while still working) and published an album of my music! 
Finally! 

They are all composed and played by me (I play electric guitar; all the rest are via MIDI) 
There are many more tunes in the pipeline; alas, it takes quite a while to finalise a tune. 
Hopefully, more will be out in the next 6 months. 

Check it out on Google Play​, SoundCloud​ ​and BandCamp

Enjoy!


ps: Comments and/or links to this article are most welcome!

Thursday, December 17, 2015

Make Logitech keyboard media keys work with AIMP and Google Play Music

If your Logitech keyboard media keys are not working, it is probably because Logitech's Setpoint software does not support your media player.

I have an MK300 MK520R keyboard and when I press the volume up/down, mute and pause keys, all is well.

However, when I press the prev/next keys, nothing happens.

Here is a solution that works with my keyboard and Setpoint

- Find the file players.ini 
(in my case, it is in:  C:\Program Files\Logitech\SetPointP)

- Open it with an editor and add the 2 lines I've highlighted
(please note that due to line-wrap, things look a little messy.
I have used <CRLF> in places where the line needs to break.
Any line breaks anywhere else are actually just word-wraps)


#List of supported players<CRLF>
#{btn|key|msg|emg|cmd|wac},exeName, wndClass,wndCaption,xxx,play_delayplay,pause,stop,prev,next,remove_flags,ShowOSD,menu_item<CRLF>
#special char --> ^ ctrl;+ shift+ctrl; $ ctrl (no translation); * alt (no translation); @ ctrl+alt (no tanslation); & (no translation) <CRLF>
<CRLF>
[Players]<CRLF>
AIMP3=cmd,AIMP3.exe,Winamp v1.x,xxx,xxx,40045,40046,40047,40044,40048,0,1,WinAmp 5<CRLF>
AIMP2=cmd,AIMP2.exe,Winamp v1.x,xxx,xxx,40045,40046,40047,40044,40048,0,1,WinAmp 5<CRLF>
MusicMatch7=wac,MMJB.EXE,MMJB:MAINWND,xxx,xxx,xxx,xxx,xxx,xxx,xxx,0,1,MusicMatch<CRLF>


[EDIT 21 jun 2014]
 Remember to restart Setpoint after you made your changes to the INI file.


[EDIT 17 dec 2015]

 Modified to suit AIMP4

[Players]
;AIMP3lib=cmd,AIMP3lib.exe,Winamp v1.x,xxx,xxx,40045,40046,40047,40044,40048,0,1,WinAmp 5
AIMP3=cmd,AIMP3.exe,Winamp v1.x,xxx,xxx,40045,40046,40047,40044,40048,0,1,WinAmp 5
AIMP2=cmd,AIMP2.exe,Winamp v1.x,xxx,xxx,40045,40046,40047,40044,40048,0,1,WinAmp 5
AIMP4=cmd,AIMP.exe,Winamp v1.x,xxx,xxx,40045,40046,40047,40044,40048,0,1,WinAmp 5




[EDIT 3 may 2017]
Modified to suit Google Play Music
In the players.ini file, add the following to the corresponding sections

[Players] GooglePlayMusic=key,chrome.exe,Chrome_WidgetWin_1,xxx,xxx,&0xB3,&0xB3,&0xB2,&0xB1,&0xB0,0,1,Google Play Music 

[Players.Run] GooglePlayMusic=path,%USERPROFILE%\AppData\Local\Google\Chrome\User Data\Default\Web Applications\_crx_icppfcnhkcmnfdhfhphakoifcfokfdhg\Google Play Music.ico 

Players.Display] 
GooglePlayMusic=Google Play Music
You then need to kill the SetPoint process and re-start it, for the changes to take effect.


Please check out my band's album on Google Play​, SoundCloud​ ​and BandCamp




ps: Comments and/or links to this article are most welcome!