Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

using Windows API Open Dialog/Browse in VBA - center form in scree

Hi,

I am using an API implementation of SHBrowseForFolder (to get a LAN
directory from the user) and GetOpenFileName API call to show the WIndows
Open Dialog (to get a filename to open).

I would like to additionally be able to center both those Windows dialogs in
the screen, and also, with the Open Dialog, I'd like to have it automatically
show the 'Details List view'

Unfortunately, calls like 'SetWindowPos', PostMessage, SendMessage,
GetWindowRect all need a pointer value for the hwnd of the owner form which
is not available in VBA Forms...

Is there anway to get that value?

thanks

Philip


Post your answer or comment

comments powered by Disqus
Hi all,

I'm working on a userform in Excel that relies on the common file
dialog windows api. Once the user selects a file, I need to determine
if the selection is valid or not. Basically, I need two additional
Windows APIs: one to determine if the file the user selected is stored
on the local machine or on a network share, and another to convert the
fully qualified name (serverfolder) to the drive letter alias set up
on the local machine (i.e. o: in this case). Has anyone used APIs
like this/have a link to a site that would have these APIs documented?
(I rarely use Windows APIs, so I need the VBA implementation of them if
possible.)

Thanks,
Johnny

Hi,

I know how to create an open dialog box in VB, but my work people only have VBA (the 'lite' code editor in comparision to VS) installed. How do I make an open dialog box in VBA considring it is so much more restricted in its abilities compared to VB.

Any help would be great.

Cheers

Sahil

Hi all,

Please share with me the code to simulate the File->Open dialog box in
VBA. Thanks so much.

Ben

--

Hi there

Does anyone know using VBA from the Excel enviroment, I would like to cause a folder on my firms network to be copied (the folder contains multiple files) and needs pasted to another location, just like one would do using windows explorer?

Has anyone any idea how one could do this, I would imagine using a windows API call although have not really used them before?

Kind Regards

Thomas

Dear all,

I am using Macro to change file type to "All Files (*.*)" in open dialog box in excel 2007. But it does not work. However it works well for excel 2000 & 2003. The following is the macro:

Sub
Open_File ()
       Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
End Sub
Appreciate that anyone could help me or give me any idea why it doesn't work in excel 2007?
Thanks!

I must obtain as data in VBA, the number of Excel files open at one time or if possible also the list with their names.
Any idea will be welcome!
Thanks!

Also posted at Mr. Excel (http://www.mrexcel.com/forum/showthread.php?t=618742)

I am using Excel 2010 and have placed a command button in my worksheet that opens a UserForm I created in VBA. The form itself has OK, CANCEL and CLEAR buttons and they all work perfectly. The form puts the data exactly where it is supposed to on the worksheet. Everything works perfectly EXCEPT my ComboBox (s) of which I have six on the form. They are all blank and I cannot figure out why. I could really use some help with this if someone has the time. Here is the code:


	VB:
	
) 
     
End Sub 
 
Private Sub BookingDTPicker_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String,
CallbackDate As Date) 
     
End Sub 
 
Private Sub CancelCommandButton_Click() 
     
    Unload Me 
     
End Sub 
 
Private Sub ClearCommandButton_Click() 
     
    Call NewBookingUserForm_Initialize 
     
End Sub 
 
Private Sub DepartureDTPicker_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As
String, CallbackDate As Date) 
     
End Sub 
 
Private Sub OKCommandButton_Click() 
     
    Dim emptyRow As Long 
     
     'Make Sheet3 Active
    Sheets(3).Activate 
     
     'Determine EmptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 
     
     'Export Data to worksheet
    Cells(emptyRow, 1).Value = NameTextBox.Value 
    Cells(emptyRow, 2).Value = ArrivalDTPicker.Value 
    Cells(emptyRow, 3).Value = DepartureDTPicker.Value 
    Cells(emptyRow, 4).Value = BookingDTPicker.Value 
    Cells(emptyRow, 5).Value = AdultsComboBox.Value 
    Cells(emptyRow, 6).Value = ChildrenComboBox.Value 
    Cells(emptyRow, 7).Value = DepositTextBox.Value 
    Cells(emptyRow, 8).Value = OrderNumberTextBox.Value 
    Cells(emptyRow, 9).Value = CreditCardComboBox.Value 
    Cells(emptyRow, 10).Value = CardNumberTextBox.Value 
    Cells(emptyRow, 11).Value = CardNameTextBox.Value 
    Cells(emptyRow, 12).Value = MonthComboBox.Value 
    Cells(emptyRow, 12).Value = YearComboBox.Value 
    Cells(emptyRow, 13).Value = SecurityTextBox.Value 
    Cells(emptyRow, 14).Value = PhoneTextBox.Value 
    Cells(emptyRow, 15).Value = AddressTextBox.Value 
    Cells(emptyRow, 16).Value = EmailTextBox.Value 
    Cells(emptyRow, 17).Value = WebsiteComboBox.Value 
     
End Sub 
 
Private Sub NewBookingUserForm_Initialize() 
     
     'Empty NameTextBox
    NameTextBox.Value = "" 
     
     'Empty AddressTextBox
    AddressTextBox.Value = "" 
     
     'Empty PhoneTextBox
    PhoneTextBox.Value = "" 
     
     'Empty EmailTextBox
    EmailTextBox.Value = "" 
     
     'Empty AdultsComboBox
    AdultsComboBox.Clear 
     
     'Fill AdultsComboBox
    With AdultsComboBox 
        .AddItem "1" 
        .AddItem "2" 
        .AddItem "3" 
        .AddItem "4" 
        .AddItem "5" 
        .AddItem "6" 
    End With 
     
     'Empty ChildrenComboBox
    ChildrenComboBox.Clear 
     
     'Fill ChildrenComboBox
    With ChildrenComboBox 
        .AddItem "1" 
        .AddItem "2" 
        .AddItem "3" 
        .AddItem "4" 
        .AddItem "5" 
    End With 
     
     'Empty CreditCardComboBox
    CreditCardComboBox.Clear 
     
     'Fill CreditCardComboBox
    With CreditCardComboBox 
        .AddItem "Visa" 
        .AddItem "MasterCard" 
        .AddItem "Discover" 
    End With 
     
     'Empty CardNumberTextBox
    CardNumberTextBox.Value = "" 
     
     'Empty CardNameTextBox
    CardNameTextBox.Value = "" 
     
     'Empty SecurityTextBox
    SecurityTextBox.Value = "" 
     
     'Empty MonthComboBox
    MonthComboBox.Clear 
     
     'Fill MonthComboBox
    With MonthComboBox 
        .AddItem "01" 
        .AddItem "02" 
        .AddItem "03" 
        .AddItem "04" 
        .AddItem "05" 
        .AddItem "06" 
        .AddItem "07" 
        .AddItem "08" 
        .AddItem "09" 
        .AddItem "10" 
        .AddItem "11" 
        .AddItem "12" 
    End With 
     
     'Empty YearComboBox
    YearComboBox.Clear 
     
     'Fill YearComboBox
    With YearComboBox 
        .AddItem "12" 
        .AddItem "13" 
        .AddItem "14" 
        .AddItem "15" 
        .AddItem "16" 
        .AddItem "17" 
        .AddItem "18" 
        .AddItem "19" 
        .AddItem "20" 
        .AddItem "21" 
        .AddItem "22" 
    End With 
     
     'Empty DepositTextBox
    DepositTextBox.Value = "" 
     
     'Empty OrderNumberTextBox
    OrderNumberTextBox.Value = "" 
     
     'Empty WebsiteComboBox
    WebsiteComboBox.Clear 
     
     'Fill WebsiteComboBox
    With WebsiteComboBox 
        .AddItem "cnn" 
        .AddItem "fox" 
        .AddItem "nbc" 
    End With 
     
     'Set Focus on NameTextBox
    NameTextBox.SetFocus 
     
End Sub 
 
Private Sub NewBookingUserForm_Click() 
     
End Sub 
 
Private Sub UserForm_Click() 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a need to used the formula:

.Range("B13").Formula = "=VLOOKUP(MAX($B$3+Cap, VALUE(TEXT(CurrentSum+A13, "0.000"))), Month11Values, 2, FALSE)"

within a VBA module.

I need to be able to do this at runtime, as I will be running different numbers through B13 as part of the macro, and only the first one can be found with this formula.

There are a few problems here:

First, note the "VALUE(TEXT(..., "0.000"))" Call. For some reason, I was having all kinds of VLookup problems stemming from fills that changed cells from .02 to .019999999999 for instance. I found this Value(Text()) call fixed that - Don't ask me why...But if you can give me an easier solution, that would be great.

Secondly, my main problem stems from the fact that I have a second set of quotes that must be within the formula around the 0.000. They need to be there as this is not actually the number 0, but a numerical format. Without those quotes, it reads it as 0 and causes an error. In VBA, it recognizes the first quote of "0.000" as the closing of the first quote in my above call. Thus, I get an error because there's more stuff after that closed quote, which VBA doesn't like.

I've tried:


	VB:
	
.Range("B13").Formula = "=VLOOKUP(MAX($B$3+Cap, VALUE(TEXT(CurrentSum+A13, " & "'0.000'" & "))), Month11Values, 2,
FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and about every other combination of single and double-quotes imaginable, but can't seem to make any of them work.

Thanks in advance for any help you can give me!

P.S. As you probably guessed, CurrentSum and Month11Values are named ranges in my WB. I am certain that the formula works when put into the worksheet manually.

I am trying to open two different program files, one after the other, in VBA
within an Excel macro.

This is what I have so far:

Shell ("C:Program FilesQuick3270Quick3270.exe" "C:Program
FilesQuick3270 IBM System.ecf")

This does not work, though. Is it because one of these files is an ecf?
Thanks

I cannot open a Floppy disc. I keep getting a message that says window
cannot open a file & to use the web service to find the solution but cannot
find. Can anyone help me out on this plese?

I am using Visual Studio .NET 2003 and I have inherited a lot of VBA mainly
for MS Excel 2003. How is it possible to use a VS 2003 .NET class in VBA 2003
when the class uses STRUCTURES. In VS 6 I could use PUBLIC TYPES in VB and
VBA to achieve what I wanted. With VS 2003 .NET class I can see members and
even see structures, BUT when I try to use one I get Variable uses an
Autoimation type not supported in Visual Basic

I cannot open a Floppy disc. I keep getting a message that says window
cannot open a file & to use the web service to find the solution but cannot
find. Can anyone help me out on this plese?

I am trying to open two different program files, one after the other, in VBA
within an Excel macro.

This is what I have so far:

Shell ("C:Program FilesQuick3270Quick3270.exe" "C:Program
FilesQuick3270 IBM System.ecf")

This does not work, though. Is it because one of these files is an ecf?
Thanks

Hi guys,

I want to add an Open Dialog Box in an excel file, so the user can browse
for a csv file and then add it as a worksheet in the original excel file.

It's going to be like this:
1. The excel file (called main.xls) has 4 Open Dialog Boxes.
2. When the user clicks on the 'Browse' button beside the first dialog box,
he can browse for a csv file.
3. Let's say he chose a csv file named file1.csv... this csv file will be
saved in a worksheet of main.xls.

How do I go about this? Please start with how I can create a Dialog Box.

Thanks!

I am using Visual Studio .NET 2003 and I have inherited a lot of VBA mainly
for MS Excel 2003. How is it possible to use a VS 2003 .NET class in VBA 2003
when the class uses STRUCTURES. In VS 6 I could use PUBLIC TYPES in VB and
VBA to achieve what I wanted. With VS 2003 .NET class I can see members and
even see structures, BUT when I try to use one I get Variable uses an
Autoimation type not supported in Visual Basic

Is it possible, in VBA, to ADD a reference to an Addin dynamically -
WITHOUT
using the Tools/Reference dialog box?

In other words:
I have an Addin (.xla) that contains functions/methods.
I wish to use these functions/methods in the VBA of a WorkBook.
I do not know where the .xla is located - except that it's in the same
folder as the Workbook itself.

And Finally, since many people will be using the Workbook, I do not
want to
ask them to 'manually' add a 'reference' to the XLA. This has to be
done automatically...

So, is it possible to Dynamically add a reference to my Add-In, using
VBA in Excel 2000 (without requiring any other add-ins if possible?)

THANKS !!
Richard

Hello

I'm currently trying to get notified in VBA if user opens/closes any
grouping on any worksheet. But there is no event in Excel2003 I could
trap.
Does anybody have any idea how I could get notified in my VBA-code.

Thanks in advance
Dirk

Hello all,

I am not a great VB programmer (my background is in C), and I cnanot get my VB code to look at a cell and return false if it has no contents and true otherwise.

Also I want it to check the cell on a different tab, what is the most efficient way of doing this without ".select"ing everything?

Cheers

p.s. Can you use the VLOOKUP excel funtion within your VBA code?

Using 2007 I was editing a workbook and used View, New Window to open the file in a second window, so I could use Arrange All to view two worksheets at once.

Now every time I open that file, it opens in two windows. I have tried closing one window, and doing an explicit save, but that doesn't work. How do I force the file to open in a single window again?

Hello

I'm a newbe, so please bear with me.

I'm having problems doing the following in VBA for Excel

A first piece of VBA script in an active Excel worksheet accesses an external DB and fills columns E from row 10 onwards with social security numbers. So far so good

A second piece of VBA script is supposed to get the first entry in the social security column (>E10) and use that value in a VBA macro that contains a sql command. The aim is for SQL to get to a second external DB and extract name and address for the person belonging to the social security number.
Name and number are to be put in column C and D of the same row the social security number was taken from.

Then the VBA script is to proceed to the next row in column E and do it all over again for the second social security number. And so on until there are no more numbers in column E.

If I put the social security number in the SQL string as an exact numerical value (like 1234567) it works. I don't now how to refer to sucessive rows in column E however.

Some stuff I'm using

Dim client As String
Dim Name As String

Const column_Client As String = "C"
Const Column_Name As String = "D"

' Column E is define as follows
Dim RgSocialSec As Range

Set RgSocialSec = Range("E10:E3000").Select

Really stuck with this
Many thanks for any help

Does anybody know some dialog box in VBA that allows to select the path.
For example, "Play Folder" in Winamp menu.

Thanks

Hi All,

Is it possible to use the "DOLLARDE" and "DOLLARFR" functions in VBA code? I'm trying to convert a dollar price expressed in fractional form into a dollar price expressed in decimal form.

Is it possible to do it using VBA code? I tried using the following:

"Application.WorksheetFunction.Dollarfr"

but it does not recognize this "DollarFR" as a valid function.

Can anyone help please?

Thank you,
Rachel

Greetings All,
Our entire department is loading a common macro workbook from a network drive using 'At startup, open all files in:' in the Tools, Options menu (EXCEL 2003). This allows me to make changes to the macros in one spot and have them propogate to all our users the next time they open EXCEL.

However, after the first user opens EXCXEL, all subsequent users get the 'File locked for editing' message. While this does not cause any problems, it is certainly annoying.

Is there another method to accomplish the same objective and/or avoid the 'file locked' message? I have tried setting the file to 'Read Only' and that did not seem to have any effect.

We will be migrating to Office 2007 later this summer so if there is a solution there, it would also be of interest.

Thanks,
Mike

Hi all,

I'm having a problem with using worksheet functions on arrays stored in vba. I use the following code to generate my array from results of iterations in Excel:

Code:
Sub PopArray()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Runs As Integer
Dim Cols As Integer
Dim Y As Double
Dim X As Double
Dim L1A As Variant

Cols = 11
Runs = Range("Runs").Value

ReDim L1A(Runs, Cols)

For i = 1 To Runs
Application.CalculateFull

    For j = 1 To Cols
    X = Range("L1_START").Offset(0, j).Value
    L1A(i, j) = X      

    Next j

Next i

Y = WorksheetFunction.Average(L1A(1, 1), L1A(1, Runs))

End Sub
I generate the two dimensional array without any issues - the problems start when I try and get the average value in the first column (last code line). I cannot seem to get the result to stay as a value.

Endgame is to generate three statistics from the array for each column: The average, the 25th Percentile and the 75th Percentile, percentiles using the LARGE function. These then to be stored as arrays and pasted into Excel.

Can anybody give me a hand finsihing off the code?

Many thanks


No luck finding an answer? You could always try Google.