Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Error using macros in excell 2007


  • Please log in to reply
4 replies to this topic

#1 ruthere89

ruthere89

  • Members
  • 16 posts
  • OFFLINE
  •  
  • Local time:10:39 AM

Posted 29 January 2012 - 05:05 PM

I am trying to instal checkboxes in all of my columns and then have each checkbox linked to the one after it. If B is checked, then A should be checked. Well This is what is so far, and Excell continues to spit an error at me.

Sub goodtogodv()
For x = 2 To 140
Set c = Cells(x, "G")
n = "Choice_" & Format(x, "00")
With ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=c.Left, _
Top:=c.Top, _
Width:=c.Width, _
Height:=c.Height)
.Name = n
.LinkedCell = c.Address
.Object.Caption = NoCaption
End With
If Cells(x, "G") = "TRUE" Then Cells(x, "F") = "TRUE"
ActiveSheet.Shapes(n).Placement = xlMoveAndSize
c.Value = False
Next x

End Sub

Most of this macro was copied from another site and it seemed to work for the column to fill down, when I tried to insert the code for the row it started to work and then threw an error in the code that previously worked.
Any help is greatly appreciated.
Thank you all.

BC AdBot (Login to Remove)

 


#2 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:09:39 AM

Posted 30 January 2012 - 08:24 AM

It would help if we knew what the error message was. :)

#3 ruthere89

ruthere89
  • Topic Starter

  • Members
  • 16 posts
  • OFFLINE
  •  
  • Local time:10:39 AM

Posted 30 January 2012 - 01:23 PM

The error was a run-time error '-2147319765 (8002802b)'

over code part

With ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=c.Left, _
Top:=c.Top, _
Width:=c.Width, _
Height:=c.Height)

This is actually in a 2003 excell document that covers five columns of code to inlay checkboxes in a fill down style.

#4 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:09:39 AM

Posted 30 January 2012 - 01:46 PM

I'm confused. Is the code that you copied created in Excel 2003 and you are trying to put in into Excel 2007, or the other way around? If the code was written for one version, and you are trying to put it into another, then it is likely the cause of the problem.

If you use your search engine and search for your error message, you will find a bunch of threads that may be helpful.

#5 ruthere89

ruthere89
  • Topic Starter

  • Members
  • 16 posts
  • OFFLINE
  •  
  • Local time:10:39 AM

Posted 30 January 2012 - 03:38 PM

The code works in 2007, not in 2003, for 2003 it will work up to the first four columns, after which it will debug and run an error message.
What I'm trying to do right now is highlight cells a and b in a certain color depending on whether or not certain criteria in c,d,or e are met and then run that down the list of 2 to 140

Here is the new code for the true false check boxes and the code that will not highlight for me.

Sub mstc701()
For d = 2 To 140
Set i = Cells(d, "G")
Set v = Cells(d, "F")
Set e = Cells(d, "E")
Set n = Cells(d, "D")
Set g = Cells(d, "C")

If i.Value = True Then v.Value = True
If v.Value = True Then e.Value = True
If e.Value = True Then n.Value = True
If n.Value = True Then g.Value = True
For s = g To i
If g.Value = False Or n.Value = False Or e.Value = False Then s.Interior.ColorIndex = 3
Next s
Next d

End Sub




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users