VBA: Remove buttons by name

'If the forms menu, use
Sub RemoveButtons()     
Dim ShapeA As Button     
For Each ShapeA In ActiveSheet.Buttons           
If ShapeA.Caption = "Doodle" Then ShapeA.Delete     
Next ShapeA
End Sub

'If from the control toolbox, use
Sub RemoveButtons()     
Dim ShapeA As OLEObject     
For Each ShapeA In ActiveSheet.OLEObjects           
If ShapeA.Object.Caption = "Doodle" Then ShapeA.Delete     
Next ShapeA
End Sub

VBA: Get pressed key

This was Basically part of a code database dump, but it is the most searched for post on here, and looking at it I don't think it's as helpful as it could be.

So, at the bottom I will leave the code that was originally here, but I will add more meaningful code and descriptions first.

For information on the codes for various keys, in the VBA editor search help for 'OnKey'.

The following will set you workbook to intercept the keys control C and fire an event.

Note that for testing you will have to click run on the open event to set it up, or close and re-open the workbook.

'Add this to the ThisWorkbook Open event

Private Sub Workbook_Open()
Application.OnKey "^{c}", "Key_Pressed"
End Sub

'Add this to a new module

Sub Key_Pressed()
'Do What You Want
End Sub

That's it. If you want to pass variables to your event, whether it is the key that was pressed or a value or anything else, you pass it along as a parameter like this.

Private Sub Workbook_Open()
Application.OnKey "^{c}", "'Key_Pressed""C""'"
End Sub

Sub Key_Pressed(key)
MsgBox key & " key was Pressed"
End Sub

'----Old Code----

Declare Function GetKeyState Lib "user32" _(ByVal nVirtKey As Long) As Integer
Const VK_CONTROL As Integer = &H11  'Ctrl
Sub test()   
If GetKeyState(VK_CONTROL) < 0 Then
Ctrl = True
Else Ctrl = False   
If Ctrl = True Then       
MsgBox "pressed"   
Else       
MsgBox "Not"    End IfEnd Sub

'And this in the sheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call test
End Sub

VBA: Open all workbooks inside a folder, run macro and save them

Sub LoopFolders()Dim oFSODim Folder As ObjectDim Files As ObjectDim file As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("c:\MyTest")
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path '<<<<< run macro here on Activeworkbook Activeworkbook.Close SaveChanges:=False
End If
Next file
Set oFSO = Nothing
End Sub

XML to Dataset

Here is some quick code illustrating how to put XML data into a dataset. Drom a datagridview on your form and drop in this code, change the table number near the end to suit your particular needs.

Imports System.IO
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'load xml into a dataset to use here
Dim dS As New DataSet
Dim fS As FileStream
'open the xml file so we can use it to fill the dataset
fS = New FileStream("C:\test\10g_2.xml", FileMode.Open)
'fill the dataset
Try
dS.ReadXml(fS)
Catch ex As Exception
MsgBox(ex)
Finally
fS.Close()
End Try
Me.DataGridView1.DataSource = dS.Tables(2)
End Sub
End Class

Copy & Paste Form Controls

My previous post worked with the chart controls in VS. While working on my own project I realized a need for the ability to copy the chart control and place it into another program such as Excel or Powerpoint.

I tried many combinations of things, but they just weren't working. Then I stumbled upon this article by Scott Lysle (http://www.a1vbcode.com/app-3902.asp) which made the whole thing easy. His method creates a .bmp image and saves it, I have adjusted it in two ways to accomplish my goal. First I turned it into a function that returned the image instead of saving it, and then I put it in the clipboard for ease of movement.

His code uses a datagridview as a source, but as he mentions in the write-up, it is easily adaptable for most any control by just changing the control type.

Here is the code to copy the datagridview that accompanies my chart:

Public Function Convertdg2BMP(ByVal dg As DataGridView)

dg.Refresh()
dg.Select()

Dim g As Graphics = dg.CreateGraphics
Dim ibitMap As New Bitmap(dg.ClientSize.Width, _
dg.ClientSize.Height, g)
Dim iBitMap_gr As Graphics = Graphics.FromImage(ibitMap)
Dim iBitMap_hdc As IntPtr = iBitMap_gr.GetHdc
Dim me_hdc As IntPtr = g.GetHdc

BitBlt(iBitMap_hdc, 0, 0, dg.ClientSize.Width, _
dg.ClientSize.Height, me_hdc, 0, 0, SRC)
g.ReleaseHdc(me_hdc)
iBitMap_gr.ReleaseHdc(iBitMap_hdc)

Return ibitMap
End Function

And here it is with the chart:


Public Function ConvertCH2BMP(ByVal ch As Chart)

ch.Refresh()
ch.Select()

Dim g As Graphics = ch.CreateGraphics
Dim ibitMap As New Bitmap(ch.ClientSize.Width, _
ch.ClientSize.Height, g)
Dim iBitMap_gr As Graphics = Graphics.FromImage(ibitMap)
Dim iBitMap_hdc As IntPtr = iBitMap_gr.GetHdc
Dim me_hdc As IntPtr = g.GetHdc
BitBlt(iBitMap_hdc, 0, 0, ch.ClientSize.Width, _
ch.ClientSize.Height, me_hdc, 0, 0, SRC)
g.ReleaseHdc(me_hdc)
iBitMap_gr.ReleaseHdc(iBitMap_hdc)

Return ibitMap
End Function

**You will need to add an imports:

Imports System.Drawing.Imaging

And then you will need this function pasted within your class:

Private Declare Auto Function BitBlt Lib "gdi32.dll" _
(ByVal pHdc As IntPtr, ByVal iX As Integer, _
ByVal iY As Integer, ByVal iWidth As Integer, _
ByVal iHeight As Integer, ByVal pHdcSource As IntPtr, _
ByVal iXSource As Integer, ByVal iYSource As Integer, _
ByVal dw As System.Int32) As Boolean
Private Const SRC As Integer = &HCC0020

Then just call the function you need based on the control (you could use just one, yes, but that is beyond the scope of this I think):

Clipboard.SetImage(ConvertCH2BMP(Me.Chart1))

I set mine up in a context menu so a right-click -> copy fires the code and then I go paste where I like.

VS 2010 Chart/Dundas Charting

I have worked on a very limited basis with Dundas (http://www.dundas.com/) charts in the past and have waited quite some time for charts to come to WinForms. VS2010 finally does this, and from what I can tell they are an exact replica of Dundas if not the actual chart themselves.

I built a project with the Dundas charts and had to tweek many of the parts, so this post will serve as a guide to working with Dundas charts and also VS2010 charts if they do prove to be identical in use as the initial project was built with Dundas, and this sample will use the WinForm chart.

Setting up data sources is the same in VS2010 as it was in previous versions so if you need more detailed instructions, refer to my earlier posts. In this sample I am using the Northwinds.mdb Products table.

Go ahead an drag a chart component onto your form, it should be in the toolbox under Data automatically. We will spend the next bit of time in the charts properties window, it is much easier to set up here than doing it entirely through code.

First thing I do is delete the Legend, you will notice it is a collection, just open it and remove. The next step is to drag the table onto the screen in the form of a DataGridView (check other posts on databinding if you don't know how), you might have to manually select it because in my Beta version None is selected by default. This creates your table adapters and binding sources etc and gives us something to look at to easily verify what we are doing.

Next step, in the properties window set your datasource to the newly created bindingsource. Now we want to modify the series collection, this is where most of the groundwork is done.

I changed the Name, made it a Line chart, and changed the X and Y value members under datasource, these only have selections available if you have binded the chart to a datasource already. This series is UnitsInStock and I will add one more showing UnitsOnOrder and will name it OnOrder:
I also changed the border width to make my line thicker and easier to see, you can also specify in this screen whether the data is on a secondary axis.

Now the chart looks OK, but there is too much data for it to be of much use. So lets filter this using a combobox to let us choose which departments to look at. Drag on a combobox and set the binding (so far in the Beta version, dragging the item from the data source does not actually bind the item) it should look like this:

Now we just need to set the logic up in code. Create an event for a combobox change and add this line:

Me.ProductsBindingSource.Filter = "CategoryID=" & Me.CategoryIDComboBox.Text

What you'll notice however is that the combobox is filled with multiples, and when you change the binding with the filter, you remove all of your selections. You can load the box manually, do buttons instead, any number of things, but I want to show you a way that you can use in other situations that aren't as straight forward.

In you DataSources window right-click on your DataSet and select Edit DataSet with Designer. Now copy the Products table and paste it, this will create another table called Products1. If you link these together, you will be able to make a selection in one which will cascade to another, very helpful but not a part of this post. In the new table choose Configure.

Hit next a couple of times until you get to the SQL and you see Query Builder. You can write the SQL yourself or take the easy route and just use the QB. In the end the SQL should look like this:

SELECT DISTINCT CategoryID

FROM Products

Note the Distinct, this will keep data from showing up multiple times. Keep hitting next and finally Finish. Next we need to update the binding on our ComboBox:

Notice we are opening the dataset and going with Products1 which is the new table we created. The code we have in the selected change is still valid so we can go ahead and run it.

Your data should change, but your chart should not. We need to rebind and update the chart, so at the bottom of the selectedChange event we will add:

Me.Chart1.DataBind()
Me.Chart1.Update()

Thats it, your chart should now update based upon your selection.

The next post will show how to tweak some things such as X and Y minimums and maximums as well as tooltips and labels.

Allow Users to Drag & Drop Controls

Many times your users want to view their data in a particular way, but most people think it is too difficult to allow them to customize or don't even think it is possible. This reasoning may come from the plethora of poor information and general ignorance found when searching for such capabilities. Most methods are ridiculously difficult, overly vebose, or a pain to implement, and as usual there is a fairly simple way to this.
First, build a form and drop on 2 textboxes and 2 buttons. Next we will build a few events. Go into the code and from the left dropdown select Button1, and from the second choose MouseDown, this creates an event for us. Do the same for MouseMove and MouseUp, both for the Button1 control only.
Rename the subs for ease of use to startDrag (MouseDown), whileDragging (MouseMove), and endDrag (MouseUp). This allows us to more easily see what we're doing, and lets .NET build the required events.
The handles will be removed later, but you need each to say Handles Button1.'event' because we want to test as we build to demonstrate.
Next, just under the Class, we need to create a few variables.

Dim dragging As Boolean
Dim startX As Integer
Dim startY As Integer

You will see what these do shortly. First, in the startDrag, set dragging to True and set the x and y coordinates:

dragging = True
startX = e.X
startY = e.Y

startX and startY are the x and y location of where the click occurred and will be used in the movement calculation, which in the whileDragging sub is:

If dragging = True Then
sender.Location = New Point(sender.Location.X + e.X - startX, sender.Location.Y + e.Y - startY)
End If
Me.Refresh()

The refresh simply forces a refresh of the screen when we're done, the work happens on the line above where as we move and get a new location for the mouse, we add the current x and y to the current x and y and subtract the initial number to get our current coordinates. If you don't know what sender means, you will when i do the post on it :) for now, just know that when you want to use the same sub for multiple controls, sender will pass in the name of the control causing the event. In this situation that sender is Button1, though that will change soon enough.
Finally on the stopDrag turn of dragging:

dragging = False

Run your program and drag Button1 around to check it out. Now you might see an issue when dragging a button, that being that once you click it and unclick it, you have fired of the buttons click event. Luckily, the buttons event is fired before the MouseUp event so you can handle the issue by placing this around the code:

If dragging = False Then
'code
End If

Next, lets set all controls to be movable. Know that you can make any control movable or not movable, and that you can easily turn on or off dragging so you can build a setup mode. I may go into that later if there is interest or I feel like it.
First, delete the Handles Button1.MouseUp etc from each of the three events. on the forms load event place this code:

For Each Control As Control In Me.Controls
AddHandler Control.MouseDown, AddressOf startDrag
AddHandler Control.MouseMove, AddressOf whileDragging
AddHandler Control.MouseUp, AddressOf endDrag
Next

This takes each control and assigns a sub to an event so that when Control.MouseDown is fired, startDrag is begun. Try out the program again and try moving bothe textboxes and both buttons. Thates it for the movement, now we have something thats even harder to find information on, and that is saving the location of the controls so when you close the program it won't reset.
First go into the Project->'project name' Properties and create a setting (more on seeting in another post). Click Settings on the left and create a new setting named controlLocations, setting the type to System.Collections.Specialized.StringCollection. Now a very important part to prevent work later, click the ellipses in the 'value' column and place a 0 in the editor and select ok, this creates the file that will store all of the location data. Now we need to populate the collection.
In the endDrag event place this:

My.Settings.controlLocations.Clear()
For Each Control As Control In Me.Controls
My.Settings.controlLocations.Add(Control.Name & "!" & Control.Location.X & "!" & Control.Location.Y)
Next
My.Settings.Save()

(After publishing I noticed that my vertical bar did not show so i used !, there is no significance other than it is a rarely used symbol we can use for delimiting text.)
That will store the x and y coordinates of all the controls, next we need to load that when the form opens, so on the form load event:

For Each Control As Control In Me.Controls
For Each item In My.Settings.controlLocations
If Split(item, "!")(0) = Control.Name Then
Control.Location = New Point(Split(item, "!")(1), Split(item, "!")(2))
End If
Next
Next

I had a flickering problem I thought I ought to bring up, if it occurs to you, make sure you have no handles on your events. I had put them back in while writing and forgot to remove them again.
There you go, we easily made a user defined form that allows for saving and loading of control locations. Enjoy.