Posts by khenzel

Hello everyone...

I dont know where else to go, so i thought i would ask if any of you might have ideas for me. I was going to design a series of UserForm VBA games to load in to my application. Purpose:

my fiance has a friend at work who's husband is going overseas. I designed a program for my fiance through excel which is a wedding countdown - displays a different image/caption/mp3 song every day with a countdown timer in days/weeks/hours/seconds until our wedding day.

My fiance thought it would be a good service for her friend if i could adapt my program for her co-worker's children, as her husband will be gone for 7 months. Her proposition for me was to re-design our wedding countdown program and insert the same - images/songs/trivia daily that updates, as well as including some fun games for the children to play each day while they wait for their father to return home.

Needless to say, i dont have the time to create many UserForm driven games that would be entertaining to the children every day for 217 days... I could create a few, like tictactoe, hangman, and whatever else, but i'm sure with 4-5 games over 217 days it would get quite boring.

I figured that somewhere out there people share code and offer such userforms they created with others... mainly trivia/games is what i have been looking for. Unfortunately for me VBA in the game department is tied in to gameboy games through google for some reason, making my search impossible!

If somebody could provide me with ideas as to where to locate userforms created to be shared with others in the area of children's entertainment/games, it would be grately appreciated, and a service to this family making sacrifices to serve!

Thanks in advance for any feedback you can provide!!

EDIT:

i was also just thinking if there was some way i could import flash files into a user form... that way i could locate flash games i could load into a user form that pops up. I think locating flash games would be much easier - and much more entertaining!

Is this doable without needing to load a custom control into excel? (needs to be combatible with other machines on the fly!)

Thanks so much!

EDIT AGAIN!!!

Nevermind, i figured it out! Just installed shockwave flash, pointed to TOOLS > Additional controls > to locate and check the
"shockwave flash object" and add it in to my form.

and i linked in a flash file like this:

ShockwaveFlash1.Movie = "http://y.kefche.com/games/gzbibe.swf"


ShockwaveFlash1.Play

just a random swf game link i found. works like a charm!!!

now i need to seek out and find flash movies/games to load daily! =)

I excel & vba!

Ok,

I'm a pronounced noob to access. I understand primary keys, and basics that come from an entry level Access college class, such as creating tables, arranging data, somewhat familiar with relationships, and so forth, but I really want to get into advanced user levels with database design, integrity, and maintenance.

My question is this: what sites are good clear understandable sites to access to pick up advanced access procedures that speak well to a novice?

I consider myself a quasi-expert with Excel... i create vba code to do whatever data management i need. I'm very familiar with VBA, but sql is new to me. Access at this point totally confuses me, so i try to make Excel adapt to all of my data manipulation needs, as i can make it sing for me.

I realize that if i knew Access better, the processes i use Excel for are probably redundant and create more work... such as table comparisons through loops and if statements.

In a summation, i want to learn access but i'm having a great deal of difficulty finding a good entry location to get started!

Please post suggestions to good starting areas here if you know of good resources. All correspondance will be very much appreciated! Thanks!

Re: Count Of #N/A Errors

Quote from PCI

Just to close the loop concerning the function code sent.
The code sent is a for a User Define Function and has to be placed in VBA:
Function Count_Error(MyRg As Range) As Integer etc...
Then use the function in your worksheet:
= Count_Error(B2:B6) for range B2 to B6
= Count_Error(B:C) for column B and C

Thanks for the suggestion. I did go with something a bit different. I called the function from my main sub like this:

vErrorCountTTL = Count_Error(Selection.SpecialCells(xlFormulas))


    If vErrorCountTTL > 0 Then
    


        vSelect = MsgBox("Warning, the processs has uncovered '" & vErrorCountTTL & "' Errors!" & Chr(13) & Chr(13) & _
                    "Do you wish to enter error check mode to correct this data before proceeding?" & Chr(13) & _
            Chr(13) & "If you select YES, you will be walked through an error check procedure." & Chr(13) & _
            "If you select NO, the report builder will finalize without correction.", _
            vbYesNo, "ERROR CHECK MODE SELECTION - Ferguson Enterprises, Inc.")
            If vSelect = vbYes Then


...

it's working very smoothly now!

I used to have to enter manual error check mode during my process with a dummy form loaded to pause code processing. I now have it modified to only prompt for error verification if it finds bizarre errors on the sheet that my automization error checking process did not solve.

It is so much more streamlined now... Plus it tells me exactly how many errors to look for if and when i need to review the data!

Thanks for your help! This function was exactly what i need. I'm not the best at objects, but when created i generally figure out how to plug them in!

Re: Check To See If A Word Appears On The Sheet

Quote from PCI

To count the number of error without distinction:

Code

Function Count_Error(MyRg As Range) As Integer
'
Dim Rg   As Range
Dim C As Variant
    Set Rg = MyRg
    Count_Error = 0
    For Each C In Rg
        If (IsError(C.Value)) Then Count_Error = Count_Error + 1
    Next C
'
End Function

Display More

Sorry to be a noob, but how do i specify the range in the above code? =)

Tried these in my main function, but none work:

vErrorCountTTL = Count_Error("A")
vErrorCountTTL = Count_Error("A:AG")
vErrorCountTTL = Count_Error(A, AG)
vErrorCountTTL = Count_Error("A, AG")

thanks!

edit:

Nevermind the above. I managed to figure it out. THanks so much for the sample! It worked perfectly! Finally got a good error check method worked in to my program! Appreciate it!!!

Re: Check To See If A Word Appears On The Sheet

Quote from Sicarii

In Excel, the IsNA function can be used to check for a #N/A (value not available) error.

The syntax for the IsNA function is:

IsNA( value )

value is the value that you want to test. If value is a #N/A error value , this function will return TRUE. Otherwise, it will return FALSE.

i.e. =IsNA(A1)

Display More

I tested that, and it works only with a single cell. This will not work for me as i need to test a range. The sheet is 1400 rows by 30 columns... checking it 1 cell at a time would increase the processing time and the difficulty of code to a undesirable extent.

Isn't there a way to check a range to find a #N/A formula error?

Hi guys,

What i'm looking to do is input a VBA command to comb the activesheet (which contains approximately 1400 rows, and 32 columns of mixed data and formulas), and determine if the text value "#N/A" appears anywhere on the sheet. If it occurs one or more times, i want it to set a boolean flag to true. Is this a possibility?

I know of ways to do so by setting a =countif(A:AG,"#N/A") formula in a cell, but unfortunately this is not my solution here, as it needs to be done before the formula is converted to text. A countif does not show a visible value of #N/A if its true value is a formula.

can this be done?

Thanks for the help!!

Hello all,

Currently i'm working on compiling a very large email distribution list to auto attach the workbook to through excel after processing.

Hitting one roadblock.

I understand i can manually compose my distribution list in this format:

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail


If vCriteria = "MIS" Then
    .To = "[email protected] ; [email protected]"
    .Cc = ""
    .Bcc = "[email protected]"
    On Error Resume Next
    .attachments.Add modISPR.varPath & "/Attachments/" & "ALL ISPR Report " & vDate & "." & Format(Now, "yyyy") & ".xls"
    On Error GoTo 0
End If

BUT the problem is that my email list is now so extensive that it carries me beyond the code line limits of VBA.

What i need to do is either pull it from separate function or from a separate file, such as a separate xls file containing simply email addresses...

I have no idea how to make this work though.

The bulk of my knowledge was to pull out the code above and put it into a separate funciton, but when i do this, once taken away from the .body and other email controls in the master function, it falls apart as from what i can tell they need to exist inside the same scope.

If anyone can help me clean this up i would appreciate it! thanks!

I'm looking for help with what i'm working on at the moment, which is the process of generating an email through Excel once the process has been completed.

My question is this:

How do i convert my process. Currently i'm coding in plain text to add to the email body:

.Body = "*********************" & frmLocSelect.vMonths & " MONTH ISPR REPORT*********************" _
    & Chr(13) & Chr(13) & "Attached is your ISPR report generated for a " & frmLocSelect.vMonths & " month period." _

what i want to do is something a bit more fancy (and a bit more line saving for my code) by simply importing the email body from a word document saved in a specific location.

I tried to run this:

.body = c:\...

but what shoots out the other side is nothing but a path in my email body.

Is there a way to tell outlook to import the email body from a document?

Thanks so much for the help!

one solution breeds 3 new problems it seems... now having another issue:

Sheets("temp").Select
                Range("F5").Offset(0, vColumnCounter).Select
                ActiveCell.Resize(4442, vColumnCounter).Select
                Selection.Copy

should work no? i need to offset column F by x amount of rows, and from there select a range spanning from X10 to X4442.

i've tried using the resize property, but it does not seem to be functioning properly. Any deviation to doing this would be perfect, as long as it selects the necessary range. Help would be awesome! Thanks.

Re: Adding Value To A Letter

Quote from Reafidy

try:

Code

'6 - 10 represents columns F - I
    For i = 6 To 10
        If Cells(5, i).Value = 1 Then Exit For
    Next i

ty got it working now. =)

i tried something a little different though...i went the path of offsets:

Do
    If vRef(vCounter) = 0 Then
        vCounter = vCounter + 1
    Else
        Do
            If Range("F5").Offset(0, vColumnCounter).Value = vRef(vCounter) Then
                MsgBox "this is correct!"
                vColumnCounter = 216
            Else
                vColumnCounter = vColumnCounter + 1
            End If
        Loop Until vColumnCounter = 216
        vCounter = vCounter + 1
    End If
    vColumnCounter = 0
Loop Until vCounter = 216

Hello Hello,

I am trying to create a loop in which the code "reads" ... lets say... cell F5.

Determines if cell F5 = 1....

if not, it moves on...

only, instead of moving to cell F6, it moves to cell G5...

so i need to add one to the column letter.

How is this best achieved? having trouble with this. adding a letter to it concatinates them, and adding numbers, well its a mismatched type so that simply does not work.

Thanks as always for any light you can shed!

Hi guys, i'm trying to currently design an array to store items using a loop procedure. This is what i have:

Public vRef(216) As Integer


Sub modReference()




'populate the reference variables off the list sheet - assigning PC values
Do
    If Sheet1.lbBranches.Selected(Sheets("list").Range("J" & vCount).Value) = False Then
        vCount = vCount + 1
    Else
        vRef(Sheets("list").Range("J" & vCount).Value) = (Sheets("list").Range("D" & vCount).Value)
        vCount = vCount + 1
    End If
Loop Until vCount = 219

this idea just does not working for me, as it is not dropping the values into the area it should.

I tried first assigning the (Sheets("list").Range("J" & vCount).Value) to a variable too, and ran it like this:

vRef(vVariable) = Sheets("list").Range("D" & vCount).Value

and this did not work either... it instead CREATED a new variable named vRev(vVariable) and set that = to Sheets("list").Range("D" & vCount).Value

here is an example of the reference file sheet "list" it is pulling data from:

D     |       J   
--------------
PC    |     REF
--------------
1      |       0

so it is assigning vRef (column J value) = column D value.

if this ends up not being possible, it will also work just as well, if not better, if i can simply add each value located to be true to the next available spot in my array as well:

for example... some psudocode:

build array with 216 objects (max range)

begin populating array:

is this item selected?

if it is then add the value at X position on the "list" sheet to the first empty location in the array.

loop to the next item, then add that one to the SECOND empy location in the array, and so on.

this psudocode would be a preferred procedure, as it would be more streamlined to generate my data in this format.

Alas, i have no idea if it is possible to have excel seek to the first available un-populated position inside an array to fill it, so i'm at a standstill right now. I could use some help!

Thanks for suggestions/commends/and help provided in advance!

EDIT:

gosh... nevermind i'm an idiot. this is what i was testing my populated variables by:

MsgBox vRef1
MsgBox vRef2
MsgBox vRef5
MsgBox vRef111

feel pretty stupid. naturally it should have been

MsgBox vRef(1)
MsgBox vRef(2)
MsgBox vRef(5)
MsgBox vRef(111)

my code does indeed work. oh well perhaps i taught somebody something at least.

=)

Hello again!

Problem i'm currently having...

I have a template i'm making that is full of formula references to sheets not yet in existance in the workbook. what i want to happen is this:

My code imports data files, renames the tabs to 1, 2, 3, 4...

The formulas i already have are set in the CONCATENATED tab.... an example of such a formula is:

='1'!$P2

so this should work, and yet it doesn't. the sheet is imported as it should be, the tab is renamed to 1, and yet the CONCATENATED sheet still shows #REF for the cell value.

if i click in the cell, click in the text bar and enter out, the formula updates. there apparently is no option that i KNOW OF (although i'm sure there is something out there that does...) that will update this without me entering into the text bar and entering out.

Question:

how do i manually update all references once the sheets to those references have been imported?

Thanks!

EDIT:

Quick addendum... recalculation does not work. I've tried F9, Shift F9, ctrl+alt+shift+f9, and changed options inside toos>options>calculation and messed around with items... nothing updates the value of these cells other then going in and actually clicking on the text bar and hitting 'enter'. aggrevating!

Re: Date Convert Xx To 20xx By Macro

Quote from Dave Hawley

For text dates/numbers format the date cells as required, copy a blank cell , then select you text dates/numbers and Edit>Paste Special - Values - Add.

Wow, it actually works! I've been assuming this isnt even possible to do for several months. Thanks Dave! You always come through for me!

Re: Date Convert Xx To 20xx By Macro

Quote from Ger Plante

Well, it all depends... but yeah, I think so...

In VBA, you can force the date format with this....

Code

activesheet.range("A1:A20").NumberFormat = "dd/mm/yyyy" 'could also be mm/dd/yyyy etc.

HTH
Ger

Display More

tried that... didnt work... the green triangle is still there. =(

i even tried breaking the data apart using text to columns... using the / as the delimiter. i then rejoined it adding the 20 in front of all years in hopes that this would make excel understand what it is. the green triangles went away, but the problem still remained:
the conditional formatting did not get understood

funny thing is, i would select the date cell "01/01/2008" and click inside the cell text entry area and simply hit enter. that would make the conditional format become recognized. i didnt even change any of the values... i just entered out!!!

why does this happen? why does excel not have any capability of recognizing dates for conditional formatting unless it is manually defined? this is quite frustrating!

the millennium bug strikes back!

I'm having some trouble... i already set up my code to conditionally format a date column if it is 5 days old (turn the text red). The problem i'm having is that these data downloads come off the server like this:

01/14/08

so i'm getting the green triangle error "text date with 2 digit year"

with the option to covert it to 19xx or 20xx.

I have to manually select this text and select 20xx before it is recognized by excel for what it is. only after this is the conditional format rule processed, and my 5 day old dates turn their appropriate colors.

When i set up a macro record while i do this date conversion, it does not record any command for this conversion.

Is there any code i can enter that will automatically do this conversion process for me without having to do it by hand?

thanks!

Re: Throw Break By Code

Quote from shg

You could pop up a two-button modeless form that prompts the user to inspect the data, and then press Finish or Cancel depending on the result.

The last thing you want unsophisticated users to be doing is staring at a bunch of VBA. That's not fancy, it's bad.

ooo good idea... i have to try that. didnt even think about that.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from shg

You could pop up a two-button modeless form that prompts the user to inspect the data, and then press Finish or Cancel depending on the result.

The last thing you want unsophisticated users to be doing is staring at a bunch of VBA. That's not fancy, it's bad.

ok so i put in a frm.show command, and apparently when the form pops up it locks the sheet behind it. how do i run it in "modeless" form as stated above?

Re: Throw Break By Code

Quote from Andy Pope

Ah, you are the user

You could just use a msgbox and then CTRL+BREAK will take you to debug mode.

i could but i love automating =)

either way works, but a yes/no is a bit more fancy i think.

i might be handing off this process later to somebody who is not familiar with advanced excel usage as well, so i want to simplify the process and make it as user friendly as possible. This was the easiest way to make it happen without programming another few hundreds of lines of code to error check my sheet to check for/auto fix data integrity issues. Unnecessary to go through all that extra effort if a quick eyeball pass can take care of the problem just as good if not better (as conditions change with each data integrity issue).

Re: Throw Break By Code

Quote from Andy Pope

have a look at the Stop statement.

Any particular reason you want users poking around your code in debug mode??

Just figured it out...

debug.assert (condition = true)

the reason is a bit complicated, but i compiled an automated report builder through excel, and toward the end of the process it removes all formulas in the sheet that i have created.

Problem is, sometimes the data downloaded from our system is buggy, and cuauses the data to become inacurate. I used to have to run it a second time and stop it before removing formulas to massage the data before completion so my formulas calculated properly.

with this stop statement i can simply select yes/no to break it without manually doing it, make the changes, and proceed with a quick ctrl+f8. makes life much easier for me.

I just had a interesting idea and i'm wondering if it is possible...

What i want to do is throw a break in my vba code if the user selects yes through a msgbox vbYesNo prompt and proceed in debug mode. is it possible to code in a break point in this fashion?

my desired pseudocode:

if user selects yes:
set break point (to send to debug mode at that point... i dont mean to end the code with a END statement.)
if user selects no:
proceed program normally

Thanks!