Re: VBA Control - Table/Grid
Excel VBA question moved to Office Development
1 Attachment(s)
Re: VBA Control - Table/Grid
One neat trick is to use an "Microsoft Office Spreadsheet xx.0" control.
Gives you everythiing that the Excel Sheet does but placed on a UserForm. :D
Re: VBA Control - Table/Grid
Be careful with the Spreadsheet control, it doesn't support ALL excel's objects, methods, collections, and properties.
For example, today I was helping someone with this object. They wanted to use AutoFilter and automatically filter a specific item, but the Spreadsheet control doesn't support "Criteria1, Field, Criteria2, etc" for Autofilter. I did manage to find a workaround, after some digging.
Incase you have a question about that, check out:
http://www.vbaexpress.com/forum/showthread.php?t=9940
And I also couldn't set a Range variable to the range on the Spreadsheet control, which was weird...I kept getting a type mismatch error.
Re: VBA Control - Table/Grid
Just dim your Range variable as an Object. ;)
VB Code:
Option Explicit
Private Sub UserForm_Click()
Dim oRange As Object
Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
MsgBox oRange.Address '"$A$1:$B$1"
End Sub
Private Sub UserForm_Initialize()
Spreadsheet1.Cells(1, 1) = "Meow!"
Spreadsheet1.Cells(1, 2) = "Meow!"
End Sub
Re: VBA Control - Table/Grid
Quote:
Originally Posted by RobDog888
Just dim your Range variable as an Object. ;)
VB Code:
Option Explicit
Private Sub UserForm_Click()
Dim oRange As Object
Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
MsgBox oRange.Address '"$A$1:$B$1"
End Sub
Private Sub UserForm_Initialize()
Spreadsheet1.Cells(1, 1) = "Meow!"
Spreadsheet1.Cells(1, 2) = "Meow!"
End Sub
Hmmm...so is it were as if I'm connecting to excel using late binding?
Re: VBA Control - Table/Grid
It appears to be the case but I really think it may be that the control uses a slightly different "Range" object with the same or similar props, methods, and functions etc. So the types may not be 100% equilivalent and cause the early binding type mismatch error.
I just use early binding to get my code in place and take advantage of the intellisense and then switch it to late binding and Im good to go.
Re: VBA Control - Table/Grid
Quote:
Originally Posted by RobDog888
It appears to be the case but I really think it may be that the control uses a slightly different "Range" object with the same or similar props, methods, and functions etc. So the types may not be 100% equilivalent and cause the early binding type mismatch error..
I see...do you think it has anything to do with the fact that it's the Web Components object model and not Excel?
Quote:
Originally Posted by RobDog888
I just use early binding to get my code in place and take advantage of the intellisense and then switch it to late binding and Im good to go.
Even if your code's written in VBA? (I can understand automating, say, access from excel...but I meant automating excel from within excel).
Re: VBA Control - Table/Grid
It could be as the object type may be named differently or just different enough to not match.
Yes, I meant Excel VBA inside of Excel VBA for just the code concerning the UserForm with the web control on it.
Re: VBA Control - Table/Grid
Quote:
Originally Posted by RobDog88
Yes, I meant Excel VBA inside of Excel VBA for just the code concerning the UserForm with the web control on it.
....for the web components you still get the intellisense even without the reference. The intellisense treats it (Spreadsheet object) as if it's a worksheet object...
Re: VBA Control - Table/Grid
Yes, but if you declare an object variable of Range and then try to set the range to a range on the web control you get the type mismatch erro like we been over. SoI was meaning that when writting the code that is directly related to the web control I do this but never removing any references as thats not needed.
VB Code:
Dim oRange As [b]Excel.Range[/b]
Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
MsgBox oRange.Address '"$A$1:$B$1"
VB Code:
Dim oRange As Object
Set oRange = Spreadsheet1.Range(Spreadsheet1.Cells(1, 1), Spreadsheet1.Cells(1, 2))
MsgBox oRange.[b]Address[/b] '"$A$1:$B$1"
Where .Address is not displayed when using late binding to get around the type error. So I use this first and then switch it over.
Re: VBA Control - Table/Grid
Ok, I gotcha. Thanks RobDog888 :thumb: