Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel Programming » Macro Bombs Excel 2000

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 06-01-2009, 05:01 PM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Macro Bombs Excel 2000

Please Register to Remove these Ads

I've developed a workbook with macros in Excel 2007 (Compatibility

Mode), Windows Vista Operating System. The purpose of the Macos

is to allow auto complete on a validated data entry field.

The macros run fine on this platform. I then copied the workbook

to my desktop, Excel 2000, Windows 2000 Professional Operating

System. One of the macros is killing Excel on this platform.

The code is as follows:

Code:
Private Sub TempCombo_KeyDown(ByVal _
                              KeyCode As MSForms.ReturnInteger, _
                              ByVal Shift As Integer)
    Dim cboTemp As OLEObject
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects("TempCombo")

    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
    Case 9
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case 13
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case Else
        'do nothing
    End Select

End Sub
and:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error GoTo errHandler1

    If Not Intersect(Target, Range("I12:I2024")) Is Nothing Then
        If Target.Cells.Offset(0, -1).Value <> "" Then
            ActiveCell.Offset(1, -7).Select
        Else: GoTo exitHandler
        End If
    End If
    If Intersect(Target, Range("F12:F2024")) Is Nothing Then GoTo 

exitHandler
    Set cboTemp = ws.OLEObjects("TempCombo")
    On Error Resume Next
    If cboTemp.Visible = True Then
        With cboTemp
            .Top = 10
            .Left = 10
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
            .Value = ""
        End With
    End If

    On Error GoTo errHandler1
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = "Categories!" + Sheets

("Categories").Range(str).Address
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
    End If
exitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.Protect
    Exit Sub
errHandler1:
    Resume exitHandler

End Sub
I found this code at http://<br /> <br /> http://www.co...DataVal14.html from a lead on

this site.

These macros run fine on platform Excel 2007 (compatibility mode),

Vista but bomb out on Excel 2000, Windows 2000. The line of code

Code:
cboTemp.Visible = False
in the TempCombo_KeyDown macro appears to be the culprit.

If I comment out this line(s), the macro runs but the ComboBox

remains visible. Not an overwhelming problem but not as neat.


I get the following error message under Excel 2000:

"Excel.exe has generated errors and will be closed by Windoes.

You will need to restart the program. An error log is being

created."

The error log reports:

Application exception occirred:
App: (pid=2180)
When: 6/1/2009 @ etc.
Exception number: c0000005 (access violation)

I've been wrestling with this for three days with various coding

but cannot get Excel 2000 to buy it.

I'v also been wrestling with the Forum for several days; I'll try

to upload the file later if I can ever get this tread posted.

Any ideas on how I can modify the code to make it compatible would

be greatly appreciated.

Thanks for you help.

John
Attached Files
File Type: xls HelpForumRev3.xls (557.0 KB, 1 views)
Last edited by jaslake; 06-03-2009 at 05:45 PM.
Reply With Quote
  #2  
Old 06-01-2009, 06:12 PM
Leith Ross's Avatar
Leith Ross Leith Ross is offline
Forum Moderator
  
Join Date: 15 Jan 2005
Location: San Francisco, Ca
MS Office Version:2000, 2003, & read 2007
Posts: 10,256
Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding
Send a message via AIM to Leith Ross
Re: Macro Bombs Excel 2000
Hello jaslake,

I haven't run the workbook in Excel 2000 yet, but I did see this error in the second macro (marked in red).
Code:
    On Error GoTo errHandler1
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = "Categories!" + Sheets
Sheets is a workbook collection object that holds all the Worksheets, Chart sheets, Dialog sheets, and Excel4Macro sheets in a Workbook. Sheets can not be used to variable. The ListFillRange must be a string that contains the Range address, and optionally the Worksheet name.
__________________
Sincerely,
Leith Ross

Remember To Do the Following....

1. Use code tags. Place [code] before the first line of code and [/code] after the last line of code.
2. Thank those who have helped you by Clicking this Icon on the Gray Title Bar

3. Please mark your post [SOLVED] if it has been answered satisfactorily.

Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Reply With Quote
  #3  
Old 06-01-2009, 06:27 PM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
Hi Leith.

I know that line of code appears to be incorrect. Please understand, I have rewritten this post several times because, apparently, your servers were (are) having a problem. Finally, the last time, I copied the text of the post to notepad so I could simply paste when I was able to finally upload the post.

That line of code got truncated into two lines as follows:

Code:
.ListFillRange = "Categories!" + Sheets

("Categories").Range(str).Address
If you delete the line break or whatever it is and make the code read

Code:
.ListFillRange = "Categories!" + Sheets("Categories").Range(str).Address

I think it will work for you. Let me know if this doesn't work for you and I will repost the code.
I should add, the macro bombs when you have selected an item in column F, Category then hit the tab key. I sure hope you can figure this out. It's been driving me nuts.

Thanks, John
Last edited by jaslake; 06-01-2009 at 07:40 PM. Reason: Add additional Information
Reply With Quote
  #4  
Old 06-01-2009, 11:57 PM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
I'm reposting this code. My last post was made from a copy/paste from notepad. I had tried to post for three days and had server problems. This code is directly from the project.

Code:
Private Sub TempCombo_KeyDown(ByVal _
                              KeyCode As MSForms.ReturnInteger, _
                              ByVal Shift As Integer)
    Dim cboTemp As OLEObject
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects("TempCombo")

    'Hide combo box and move to next cell on Enter and Tab
    Select Case KeyCode
    Case 9
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case 13
        cboTemp.Visible = False
        ActiveCell.Offset(0, 1).Activate
    Case Else
        'do nothing
    End Select

End Sub
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error GoTo errHandler1

    If Not Intersect(Target, Range("I12:I2024")) Is Nothing Then
        If Target.Cells.Offset(0, -1).Value <> "" Then
            ActiveCell.Offset(1, -7).Select
        Else: GoTo exitHandler
        End If
    End If
    If Intersect(Target, Range("F12:F2024")) Is Nothing Then GoTo exitHandler
    Set cboTemp = ws.OLEObjects("TempCombo")
    On Error Resume Next
    If cboTemp.Visible = True Then
        With cboTemp
            .Top = 10
            .Left = 10
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
            .Value = ""
        End With
    End If

    On Error GoTo errHandler1
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            .ListFillRange = "Categories!" + Sheets("Categories").Range(str).Address
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
    End If

exitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    ActiveSheet.Protect
    Exit Sub
errHandler1:
    Resume exitHandler

End Sub
The rest of my original post still applies. The macro bombs when you have selected an item in worksheet "Check Register", column F, Category then hit the tab key. I sure hope you can figure this out. It's been driving me nuts.
Hopefully you can help.

Thanks, John
Reply With Quote
  #5  
Old 06-02-2009, 01:16 AM
Leith Ross's Avatar
Leith Ross Leith Ross is offline
Forum Moderator
  
Join Date: 15 Jan 2005
Location: San Francisco, Ca
MS Office Version:2000, 2003, & read 2007
Posts: 10,256
Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding
Send a message via AIM to Leith Ross
Re: Macro Bombs Excel 2000
Hello John,

You were right about the server problems. Everything should be back to normal according to forum's owner. I have been occupied about all day with other things and just back to look at the posts. If you could post the workbook, it would make it faster to troubleshoot the trouble the problem. I have Excel 2000 and can run it as is.
__________________
Sincerely,
Leith Ross

Remember To Do the Following....

1. Use code tags. Place [code] before the first line of code and [/code] after the last line of code.
2. Thank those who have helped you by Clicking this Icon on the Gray Title Bar

3. Please mark your post [SOLVED] if it has been answered satisfactorily.

Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Reply With Quote
  #6  
Old 06-02-2009, 01:30 AM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
I thought I had posted the workbook. In the event not, I am uploading it again.

Thanks Leith.

John
Attached Files
File Type: xls HelpForumRev3.xls (556.5 KB, 7 views)
Reply With Quote
  #7  
Old 06-02-2009, 02:05 AM
Leith Ross's Avatar
Leith Ross Leith Ross is offline
Forum Moderator
  
Join Date: 15 Jan 2005
Location: San Francisco, Ca
MS Office Version:2000, 2003, & read 2007
Posts: 10,256
Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding
Send a message via AIM to Leith Ross
Re: Macro Bombs Excel 2000
Hello John,

This time there shouldn't be any server related snafus with the workbook. I rather be sure the only problems with the workbook are the original ones.

Thanks,
Leith Ross
Reply With Quote
  #8  
Old 06-02-2009, 02:22 AM
Leith Ross's Avatar
Leith Ross Leith Ross is offline
Forum Moderator
  
Join Date: 15 Jan 2005
Location: San Francisco, Ca
MS Office Version:2000, 2003, & read 2007
Posts: 10,256
Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding
Send a message via AIM to Leith Ross
Re: Macro Bombs Excel 2000
Hello John,

It runs using Excel 2000, but the combo box remains visible after clicking on another cell. I made several entries in column "I" and "J" and it didn't error.
__________________
Sincerely,
Leith Ross

Remember To Do the Following....

1. Use code tags. Place [code] before the first line of code and [/code] after the last line of code.
2. Thank those who have helped you by Clicking this Icon on the Gray Title Bar

3. Please mark your post [SOLVED] if it has been answered satisfactorily.

Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Reply With Quote
  #9  
Old 06-02-2009, 02:34 AM
Leith Ross's Avatar
Leith Ross Leith Ross is offline
Forum Moderator
  
Join Date: 15 Jan 2005
Location: San Francisco, Ca
MS Office Version:2000, 2003, & read 2007
Posts: 10,256
Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding Leith Ross Has a higher level of understanding
Send a message via AIM to Leith Ross
Re: Macro Bombs Excel 2000
Hello John,

I get the same behaviour in Excel 2003. The text box remains visible, and Excel doesn't crash.
__________________
Sincerely,
Leith Ross

Remember To Do the Following....

1. Use code tags. Place [code] before the first line of code and [/code] after the last line of code.
2. Thank those who have helped you by Clicking this Icon on the Gray Title Bar

3. Please mark your post [SOLVED] if it has been answered satisfactorily.

Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Reply With Quote
  #10  
Old 06-02-2009, 02:43 AM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
Leith...make an entry in column F then TAB out of it to column G. This is where things go south on me.

Does your system duplicate that? If not, what are you running for an operating system? I've tried this on two different computers today, both running Windows 2000 and Excel 2000 with the same result (Excel shuts down).

Please look at the code for Check Register to make certain I've not commented out
Code:
cboTemp.Visible = False
. If I have, please remove the ' and try to rerun.


If you can't duplicate the problem, I'm not sure where to go next.

Thanks, John
Reply With Quote
  #11  
Old 06-02-2009, 02:50 AM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
I don't understand. The ComboBox should disappear when you tab to the next column. That's what the code says. That particular line is the one that caused my system (and the other one I ran today) to crash.

You're not getting that?

John
Reply With Quote
  #12  
Old 06-02-2009, 03:14 AM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
I tried this on my resident copy. If you CLICK out of the cell (column F), the ComboBox does stay visible. If you TAB out of it, it disappears.

What say you?

John
Reply With Quote
  #13  
Old 06-03-2009, 01:03 AM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
I don't know where we are on this. I know that you told me
Quote:
the combo box remains visible after clicking on another cell.
and i asked you to
Quote:
make an entry in column F then TAB out of it to column G.
.

The ComboBox will indeed remain visible it you click out of it. It doesn't trigger the macro. Only a TAB or ENTER key will trigger the macro.

Have you tried the TAB key after entering data in column F? If so, what happens. Does Excel 2000 crash? Does the ComboBox disappear?

When you have time, please give me some feedback.

Thanks, John
Reply With Quote
  #14  
Old 06-03-2009, 06:00 PM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
Bump no response
Reply With Quote
  #15  
Old 06-04-2009, 08:14 PM
jaslake jaslake is offline
Valued Forum Contributor
  
Join Date: 21 Feb 2009
Location: mineral city, ohio
MS Office Version:Excel 2007; Excel 2000
Posts: 694
jaslake is attaining expert status jaslake is attaining expert status
Re: Macro Bombs Excel 2000
Bump no response
Reply With Quote


Reply

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Logout
Contact Us - ExcelTip.com - Archive - Top