Posts by Joe Derr

Based off of the current thread:
http://www.ozgrid.com/forum/showthread.php?t=75173

I borrowed some of the formula to help rectify my problem, however I need to add certain # of days depending on what day of the week it is. Here is what I have so far.

=IF(WEEKDAY(M7)=4,M7+5,IF(WEEKDAY(M7)=2,M7+2, M7+1))

M7 is a date entered by the user, This formula is in N7.

If the date is a Monday or a Wednesday, the formula works...

If its a Tuesday, it works (M7+1).

However if the date is a:
Thursday
Friday
Saturday
Sunday

I need the formula to determine that and add the appropriate # to bring it back to that monday.

For example, person enters 16 Aug 07 (Thursday) it needs to add 4 days to bring it back to display 20 Aug 07.

Any ideas, I think I would run out of IF statements for this formula.

Joe

Re: Hotspot A Graphic?

Good thought.. you rock.. I will try that out.

-- Worked Thanks!

I assume there isn't but like in HTML you can hotspot a graphic for a link.. is there a way to do this in Excel/VBA/User Forms ??

thanks!

Joe

I have a problem, I searched the archives, I thought the answer or close to it would be in there, but after searching multiple pages, and alot of actual repeat posts, I couldn't find it.

I have a workbook (open) that I update...

at a press of a button on the userform, I want to update a (closed) workbook with the data from the (open) workbook.

I have the code below that pulls in the data from the (closed) workbook to the (open) workbook for updating, now I just need to re-write it back to the closed workbook.

Private Sub CommandButton1_Click()
CopyFromClosedWB "I:\Data\WEEKEND.xls", _
    "Weekend", "E10:G24", Range("E10")
End Sub


Sub CopyFromClosedWB(strSourceWB As String, _
    strSourceWS As String, strSourceRange As String, _
    rngTarget As Range)


' copies information from a closed workbook, no input validation!


Dim wb As Workbook
    
    Application.ScreenUpdating = False ' turn off the screen updating
    Application.StatusBar = "Copying data from " & strSourceWB & "..."
    On Error Resume Next ' ignore errors
    ' open the source workbook, read only
    Set wb = Workbooks.Open(strSourceWB, True, True)
    On Error GoTo 0 ' stop when errors occur
    If Not wb Is Nothing Then ' opened the workbook
        On Error Resume Next ' ignore errors
        With wb.Worksheets(strSourceWS).Range(strSourceRange)
            .Copy rngTarget
        End With
        On Error GoTo 0 ' stop when errors occur
        wb.Close False ' close the source workbook without saving changes
        Set wb = Nothing ' free memory
    End If
    Application.StatusBar = False ' reset status bar
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

I retrieve from the closed workbook range E10:G24 and then I update it on the (open) workbook and now I need it to update the closed workbook at the press of a button. Button 1 = Retieve data (see code above)
Button 2 = Update Master (need help on this part)

Thanks!

Re: Data Validation Multiple Columns Data

Thanks, I can get it to work sometimes, sometimes it just selects the bottom most data to.. your's works, its mine that I am having the problem, that is exactly what I need it to do, thanks again!

I have a question about data validation drop downs.

I have 2 columns that need to be filled in once a name is selected from the data validation drop down. The data is to the right of the name in the source area.

Column A Column B Column C
1 Name 1 Skill 1 Phone #
2
3
4

This is the format, Every row in Column A has a Data Validation Drop Down, I need Column B and C filled with Data from the selected name from Column D (the data columns D-F)

Column D Column E Column F
1 Name 1 7 555-1111
2 Name 2 5 555-2222
3 Name 3 3 555-3333
4 Name 4 4 555-4444

So when I select say name 3...

Column A Column B Column C
1 Name 3 3 555-3333
2
3
4

Is this possible? If so, can some one enlighten me?

Thanks!

Re: Trim Month Year For Conditional Formatting

LOL.. don't know if that is a good thing or a bad thing.. I put in the code late last night and it was still showing the same way.. I probably was missing something.. I am going to look into again here soon, got some sleep

UPDATE:

I changed how the textbox was initially populated to mm/dd/yy format and it works. If it could goto the mm/yy it would be the best, but this works just fine and *Thank You* very much!!!:cheers:

Re: Trim Month Year For Conditional Formatting

I will give that a whirl.

The reason I am storying in month/year is for the expiration of the expense card eg 03/08 with credit cards I don't know if it matters if its the 1st of the month or the last day.

I am going to try this and then head home, I will post more tomorrow.

I want to say a big *Thank You* for all your help!

Joe

Re: Trim Month Year For Conditional Formatting

The application is a card file program, contact and information on my employees. The date (textbox24) is for their expense account card, the expiration date.

What I am attempting to do is, when that date is overdue say 01/07 the textbox will be red, when its within two months ie 04/07 it will be yellow and when its greater than 04/07 (three or more months from being due) it will just be plain white.

That is what I am trying to do.. what its doing is I have 3 test dates

01/07
03/07
03/08

The 01/07 is red like its supposed to be
The 03/07 is yellow like its supposed to be
The 03/08 (over a year out) should be white, but it also is yellow.

Simple, but yet so not working

Joe

Re: Trim Month Year For Conditional Formatting

*scratching head*

Well.. not sure.. I will have to put it down for the day and go back to it tomorrow, my brain is fried. I have been at this all day trying one aspect to the other. I will re-attack tomorrow.

The below code is how I get the date mm/yy style into the textbox24..

I just tried to assign a variable to Textbox24, but it didn't work..

' Expiration Date
 ExpirationDate = Worksheets("Database").Cells(userow, usercolumn + 36).Value
 ExpirationDate = Format(ExpirationDate, "mm/yy")
 TextBox24.Value = ExpirationDate

*reaching for asprin*

Joe

Re: Trim Month Year For Conditional Formatting

If you msgbox CurrentDate it comes out to 02/07/07
If you msgbox MonthPlus it comes out 04/07

MonthPlus is correct (as in month and year only)

I would like CurrentDate to do the same (month and year only)

I think it might be the reason I can't get the conditional formatting right...

Red is less than current date (that works actually)
Yellow if its greater than Currentdate but less than MonthPlus (works)
White if its greater than MonthPlus (doesn't work)

Having trouble with a date format:

I am trying to get CurrentDate from a 02/07/07 to 02/07

My CurrentDate = Format(CurrentDate, "mm/yy") doesn't seem to work, no matter what CurrentDate = mm/dd/yy

My eyes are watering from stareing at the screen for so long....

Any help would be apprciated...

Private Sub TextBox24_Change()
Dim MonthPlus, CurrentDate As Date
Dim userow, usercolumn As String


userow = ComboBox1.ListIndex + 2
usercolumn = 1


CurrentDate = Now()
CurrentDate = Format(CurrentDate, "mm/yy")
MonthPlus = DateSerial(Year(Date), Month(Date) + 2, Day(Date))
MonthPlus = Format(MonthPlus, "mm/yy")


MsgBox CurrentDate


If TextBox24.Value <= CurrentDate Then
 TextBox24.BackColor = vbRed
ElseIf TextBox24.Value > CurrentDate And TextBox24.Value < MonthPlus Then
 TextBox24.BackColor = vbYellow
Else: TextBox24.BackColor = vbWhite
End If
End Sub

Re: Adding Two Months To Date

That did it, you rock!!!

I am stuck, I am trying to take "currentdate" which is formatted "mm/yy" to add two months to the "currentdate"

monthplus is my variable to get the currentdate plus 2 months. I have tried various solutions, none work, this will all have to be done in vba, as writing to a sheet isn't practical. I tried to use "evaluate" to put a formula in vba but it wasn't working.

A combobox is used to fill the textbox (eg the userow and usercolumn)

the formatting below that is just to change the textbox different colors if the date is older than today (red) within 2 months (yellow) and further out than two months (white)

Private Sub TextBox24_Change()


Dim MonthPlus, currentdate as date
Dim userow, usercolumn as string


userow = ComboBox1.ListIndex + 2
usercolumn = 1


currentdate = Format(Now, "mm/yy")


MonthPlus = ??????????????????????


MsgBox (MonthPlus) ' used to show me what is going on..


If TextBox24.Value <= currentdate Then
 TextBox24.BackColor = &HFF0000
ElseIf TextBox24.Value > currentdate Then
 TextBox24.BackColor = &HFFFFFF
ElseIf TextBox24.Value > currentdate + 2 Then ' tried to add 2 but knew it wasn't going to work like this...
 TextBox24.BackColor = &HFF00FF
End If

Re: Refresh Web Browser

Taking this to a different level, changed the way the file is read, instead of reading a text file from the website, and putting into a query table, I am loading the webpage as a excel file and reading in the code from A1, then copying it to my workbook Data sheet Y2, then comparing the numbers.

However, the webpage isn't refreshing, the code above with the refresh statement errors, the Reference is set.. can anyone get it to work, and maybe post and example file?

Private Sub CommandButton1_Click()
' This function downloads the .htm file with the current version from the website
' Puts the # in Cell Y2 on the data sheet. It compares that # to the text of the
' label 18 which is this programs version, if its less than the downloaded text
' file version it will tell user to seek the upgrade
' 11-29-2006
' Updated 12-23-07


Dim a$, b$, c$, d$, e$, f$, i As Integer
Dim ThisVersion, CurrentVersion, CurrVersionE, ThisVersionE, TVersion, CVersion As Integer


' Turns off Screen Updating so view doesn't show progress
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    Workbooks.Open Filename:= _
        "http://af2005program.com/currentversion.htm"
    CurrentVersion = Range("A1").Value 'Read the Version from the website
    ActiveWorkbook.Close
    Sheets("Data").Range("Y2").Value = CurrentVersion ' Data sheet Cell Y2 is Website Version


'Reads in the versions
ThisVersion = Label18.Caption ' The label contains this version number to compare to website


'Special Thanks to Aaron Blood of Ozgrid forums
'for the fix on removing any thing other than Alpha Numeric
'characters - In this case removes all the dots in the versions
'for instance 5.9.8.2.1 would be 59821
'http://www.ozgrid.com/forum/showthread.php?t=55082


    a$ = ThisVersion
    For i = 1 To Len(a$)
        b$ = Mid(a$, i, 1)
        If b$ Like "[A-Z,a-z,0-9]" Then
            c$ = c$ & b$
        End If
    Next i
    TVersion = c$
    
        
    d$ = CurrentVersion
    For i = 1 To Len(d$)
        e$ = Mid(d$, i, 1)
        If e$ Like "[A-Z,a-z,0-9]" Then
            f$ = f$ & e$
        End If
    Next i
    CVersion = f$


' Special Thanks to Norie and Tom of Ozgrid forums
' for the fix on the formula code. - 1-24-07
' http://www.ozgrid.com/forum/showthread.php?p=327829&posted=1#post327829


' Formulas to make the 4 digit a 5 digit for comparison
CurrVersionE = Evaluate(CVersion & " * 10 ^ (4 - Int(Log(" & CVersion & ")))")
ThisVersionE = Evaluate(TVersion & " * 10 ^ (4 - Int(Log(" & TVersion & ")))")


' If the version #'s are the same, then tell them
' Else if this version is less than website version then tell them to visit website to download upgrade
' Else the version is greater than official version


If ThisVersionE = CurrVersionE Then
MsgBox ("This version is : " + ThisVersion + Chr(13) + Chr(13) + "Current version is : " + CurrentVersion + Chr(13) + Chr(13) + "You have the most current version.")
ElseIf ThisVersionE < CurrVersionE Then
MsgBox ("Time to Upgrade!" + Chr(13) + Chr(13) + "This version is :" + ThisVersion + Chr(13) + Chr(13) + "Current version is :" + CurrentVersion + Chr(13) + Chr(13) + "Please visit website on About page to download the most current version.")
Else
MsgBox ("Unauthorized copy of this program!" + Chr(13) + Chr(13) + "The official version is " + CurrentVersion + Chr(13) + Chr(13) + "This Version is " + ThisVersion)
End If


' Turns Screen Updating back on
Application.ScreenUpdating = True
End Sub

Re: Vba Formula Variable

Norie & Tom,

Its working now, the formula part, once I plugged in Norie's code for the formula, but here is the code so you can see it..

I am off to see about why I can't get it to update the webpage, I can change it, but it will show the old version # until I manually go in and refresh the webpage. I guess to test, you can change the path to a local file?! It will seem to work for you until I can get home to update the file # again.

As said above to Tom, I am going to post in a new post.

Private Sub CommandButton1_Click()
' This function downloads the .htm file with the current version from the website
' Puts the # in Cell Y2 on the data sheet. It compares that # to the text of the
' label 18 which is this programs version, if its less than the downloaded text
' file version it will tell user to seek the upgrade
' 11-29-2006
' Updated 12-23-07


Dim a$, b$, c$, d$, e$, f$, i As Integer
Dim ThisVersion, CurrentVersion, CurrVersionE, ThisVersionE, TVersion, CVersion As Integer


' Turns off Screen Updating so view doesn't show progress
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.CutCopyMode = False
    Workbooks.Open Filename:= _
        "http://af2005program.com/currentversion.htm"
    CurrentVersion = Range("A1").Value 'Read the Version from the website
    ActiveWorkbook.Close
    Sheets("Data").Range("Y2").Value = CurrentVersion ' Data sheet Cell Y2 is Website Version


'Reads in the versions
ThisVersion = Label18.Caption ' The label contains this version number to compare to website


'Special Thanks to Aaron Blood of Ozgrid forums
'for the fix on removing any thing other than Alpha Numeric
'characters - In this case removes all the dots in the versions
'for instance 5.9.8.2.1 would be 59821
'http://www.ozgrid.com/forum/showthread.php?t=55082


    a$ = ThisVersion
    For i = 1 To Len(a$)
        b$ = Mid(a$, i, 1)
        If b$ Like "[A-Z,a-z,0-9]" Then
            c$ = c$ & b$
        End If
    Next i
    TVersion = c$
    
        
    d$ = CurrentVersion
    For i = 1 To Len(d$)
        e$ = Mid(d$, i, 1)
        If e$ Like "[A-Z,a-z,0-9]" Then
            f$ = f$ & e$
        End If
    Next i
    CVersion = f$


' Special Thanks to Norie and Tom of Ozgrid forums
' for the fix on the formula code. - 1-24-07
' http://www.ozgrid.com/forum/showthread.php?p=327829&posted=1#post327829


' Formulas to make the 4 digit a 5 digit for comparison
CurrVersionE = Evaluate(CVersion & " * 10 ^ (4 - Int(Log(" & CVersion & ")))")
ThisVersionE = Evaluate(TVersion & " * 10 ^ (4 - Int(Log(" & TVersion & ")))")


' If the version #'s are the same, then tell them
' Else if this version is less than website version then tell them to visit website to download upgrade
' Else the version is greater than official version


If ThisVersionE = CurrVersionE Then
MsgBox ("This version is : " + ThisVersion + Chr(13) + Chr(13) + "Current version is : " + CurrentVersion + Chr(13) + Chr(13) + "You have the most current version.")
ElseIf ThisVersionE < CurrVersionE Then
MsgBox ("Time to Upgrade!" + Chr(13) + Chr(13) + "This version is :" + ThisVersion + Chr(13) + Chr(13) + "Current version is :" + CurrentVersion + Chr(13) + Chr(13) + "Please visit website on About page to download the most current version.")
Else
MsgBox ("Not a valid copy of this program!" + Chr(13) + Chr(13) + "The official version is " + CurrentVersion + Chr(13) + Chr(13) + "This Version is " + ThisVersion)
End If


' Turns Screen Updating back on
Application.ScreenUpdating = True
End Sub

Re: Vba Formula Variable

does it matter if the variable is dim'd as an integer ?

This is trying to check for a new version from the website (another post)

I have a routine that removes the dots once the .'s are removed, say 5.8.9.5 would be read into this formula as 5895

This formula then would add a fifth digit, so it would be 58950 and it does this because the version could expand to 5 digits eg. 5.8.9.5.1 so it will be 58951 compare that to 58950 and you can see the first one is more current..

getting off track..

Does it matter for the Evaluate if its an integer or ??

Re: Vba Formula Variable

Thanks.. I changed the Variable Name and didn't Dim the new name..

Any idea on how to either Dump a webpage, or refresh it??

I will probably start another thread so It will follow the title correctly.

I am trying to limit the amount of stuff I write to a sheet, and I figured I can just pass variables around inside of a routine with out having to write each step to a sheet like I am doing now.

Below is the formula showing Z2 as where parsed data is

CVersionFormula = Evaluate("Z2 * 10 ^ (4 - Int(Log(Z2)))")

and here it is, with me trying to plug in a variable rather than read it from the data sheet. The variables for ease are read from the data sheet. In this example I would read in Z1 and assign it "CVersion", then the formula then takes CVersion (a 4 digit number) and makes it a 5 digit number..

CurrVersionE = Evaluate("CVersion * 10 ^ (4 - Int(Log(CVersion)))")

But I don't think I have the variables in there right.. Ideas ??

Joe

Re: Refresh Web Browser

I found that the reason for the error was the that it was calling a .htm file but the qualifier (if you will) was document.title....

Take a look here..
http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/58daadac2370a72c/bf2314cfa00cfc5b?lnk=st&q=VBA+BROWSER+REFRESH&rnum=1#bf2314cfa00cfc5b

However, it still doesn't refresh the web-browser, thus my query table still doesn't show the new data.. any ideas.