Posts by ExcelJunkie

    Basic version checking subroutine which you can call at form invokation. Result is displayed back to form in user message box of some type. Enjoy.

    Simple but goodie. Send DOS Commands from VBA. This examples assumes you have the net send service running. SP2 disables this service by default. Obviously you can send any DOS command that you like, this is just a simple example.

    Sub Send_CMD_DOS()
    'DOS SHELL SEND TO VBA*******************
    strDOSCMD = "net send your_network_id hello world"
    sCmdLine = strDOSCMD
    dblRetVal = Shell(sCmdLine, vbHide) 'VBHIDE HIDES SHELL WINDOW (BLACK DOS WINDOW)
    End Sub

    Populate forms from hidden XLA worksheet objects

    Advanced Concept. Sometimes you have macros which need user parameters or your form objects such as listboxes need a list to load. Instead of storing the info in .txt or .ini text files store the info directly in your xla. The trick is reading the info from the xla. worksheets. As most know, as soon as you save your xls workbook as an xla your worksheets magically disappear. However, in reality they still exist as objects. Here are the steps

    1) Build your code/form or whatever in your XLS workbook. This will be called your "Master Workbook".
    2) Create a worksheet that has your list of parameters or whatever your macros need.
    3) Save the XLS workbook as XLA. Voila your done.

    In the sample you have the Master XLS as well as the XLA. Open the XLS and see the simple parameter list and see how it is stored on a worksheet. Next, close the XLS, and open the XLA. Do a ALT+F11 to open the visual basic editor then go to the mMain module. From the mMain module run the "ShowForm" macro. Your form will load and feed from hidden xla worksheet object.


    'Method 1 - Read XLA worksheet values
         strTest = ThisWorkbook.Sheets("shtFormFeed").Cells(2, 1).Value
    'Method 2 - Read XLA worksheet values
            With ThisWorkbook.Sheets("shtFormFeed")
                strTest = .Cells(2, 1).Value
                strTest2 = .Cells(2, 2).Value
            End With

    Archived Projects…y/DOWNLOAD/downloads.aspx

    Got to say, instead of messing around with API for finding logged in windows user name, simply use this short code. So simple my grandma could use it.

    Function UNameWindows() As String
        UNameWindows = Environ("USERNAME")
    End Function

    Couldn't get much simpler. Also many other things which can be extracted from the ENVIRON command. Here they are.

    Code to View All Available ENVIRON info

    Dim nCount As Integer
    nCount = 1
        Do Until Environ(nCount) = ""
            Cells(nCount, 1).Value = Environ(nCount)
               nCount = nCount + 1
    End Sub


    Function to generate random colors. Also, can specifically exclude list of colors from list. Additionally, added macro to generate reference table of colors.

    [hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Note, Random color generator can be set to 56....

    Excel XLA Installation - Distribution package. This is a version of my pseudo Excel Installation Package for distribution of your macros!

    Design Theory:
    Ok, without getting all crazy with the registry and such, package emulates an installation package. Tried to design with noobs in mind. Heavily commented

    with decent amount of error checking. This is a hybrid of my thin weight installation package which has been modified and intesified. When opened .xla is actually hidden window.

    What is it?
    Allows you to package up your Excel XLA into a self contained installation file. Installation process saves file as XLA, then creates toolbar with new button which activates your macro.

    How does it Work?
    Simple, simply import your forms, and modules, configure the values in Public module.

    -Customizable Button to activate your macro or form
    -Customizable word Art Text for installation proccess
    -Customizable Background color for installation proccess
    -Package has been optimized so that very minimal configuration to deploy your macro.
    -Simply configure required public vars, and import your forms and modules.
    -Cool interface to give users feel of actual installation.

    Important Notes:
    -Hidden Help File, simply format->Sheet->Unhide->Help
    -Be sure to configure all required vars
    -To see how it works, put a break point in it and step through it
    -Install package is designed to launch a SINGLE form or macro. Advanced users could probably modify it to do multiples

    To see it in action, download file, and hit the install button. Then once you see how it works, modify it with your package. As always, open source, just ask you don't remove my credit. Modify how you like!

    [Blocked Image:…ary/DOWNLOAD/ExcelXLA.jpg]

    To large for post

    Archived Downloads
    Happy Coding............

    Ever try to build a excel dashboard or report and you never can get everything the right size and position. I had that same problem, I have designed this tool to allow you to exactly position and size all objects on your active worksheet. Through some sample object in workbook, but obviously you could use the tool to design a report or dashboard. Enjoy.
    [Blocked Image:…0Library/DOWNLOAD/aod.jpg]

    Archived downloads…y/DOWNLOAD/downloads.aspx

    Sure to enjoy this one. Lets you create Arrays within Arrays. Believe it or not, a very handy piece of code. Can be a memory hog though so use judiciously. To understand it fully, open your immediate window, and locals window then put a break in the code to step through it, and watch the variables.

    Ok, I posted simplistic code to log into i-google or other page.

    Problem is after testing I found that some pages never reach an IE ready state of 4, and only 3. Thus I tweaked the code so it auto toggles back to 3 and added DOM object detection to ensure proper object selection. Enjoy. Was geekin out a little, sorry.

    Here is a short but sweet script to login to i-Google. Script could be changed to suit a variety of needs. Beware of pages that have login objects located within a frame. That gets a little bit trickier. To change to other sites simply change web address, and ie form object names to match that of your login page. To get these go to your login page, right click view source, and get form object ids.

    This is an Access script which I commonly use to retreive data from Access DAtabases. Has been heavily commented to aid newbies in their quest for data. In addition, you can encase it into a loop if you prefer. Anyway, enjoy...

    Re: Run Command Button Click From Another Command Button

    humm, getting sidetracked. The goal here is creating a button, which the user can choose will fire a series of buttons when pushed. Basically it is like the Record macro button. Let say one user wanted buttons A, B, C fired when they pushed the super button. No another user may want C, B, A, D fired when they push the super button. I was trying to avoid endless barage of if statements. Basically the button names could be stored in configuration file as text strings and would load @ runtime for any users form. Thus a single function to activate the click command based on the text strings.

    Let me say I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?

    Dim strButname as string
    strButname ="But1"

    Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?

    This doesn't work, but gives you an idea of what I am trying to do.