Hi,
I have written a visual basic script that uses Excel to store 'recipe'
data. The user is able to see currently stored recipes in a combo box.
I've recently been asked to sort the recipes so that they appear in the
combo box in alphabetical order. This is where I'm having problems and
I wondered whether anyone reading this can point me in the right
direction?

This is my script, when the form is initiated I'm getting a "Run-time
error 1004: Method 'range of object '_Global' failed.



Private Sub UserForm_Initialize()

Dim intNextRow As Integer
Dim strRowID As String
Dim strMixID As String
Dim LastRow As String
Dim WriteRow As String
Dim WriteRange As String

'On Error Resume Next

intNextRow = 4

Set ExcelAppRecipe = GetObject(, "excel.application") ' Set Reference
to currently open Excel Application
ExcelAppRecipe.Application.Sheets("Recipe").Activa te

LastRow = ExcelAppRecipe.Selection.SpecialCells(11).Row
WriteRow = "" & "5" & ":" & LastRow & ""
WriteRange = "" & "A" & LastRow & ""

ExcelAppRecipe.Rows(WriteRow).Select
ExcelAppRecipe.Range(WriteRange).Activate

ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending


strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Do While strMixID < ""
cboSelectMix.AddItem strMixID
intNextRow = intNextRow + 1
strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Loop
Set ExcelAppRecipe = Nothing
End Sub

I'm pretty sure it has something to do with the line
ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
but I'm not sute what I've done wrong? If I look on the excel sheet it
has selected the correct area but hasn't sorted it. Any ideas?

Thanks in advance

On a whim (I program in VBA, not VB script) I am wondering if perhaps you
need to refer to the sheet as well? So, Activesheet.Range("B5") as opposed
to just Range("B5")?


"marshy26" wrote in message
ups.com...
Hi,
I have written a visual basic script that uses Excel to store 'recipe'
data. The user is able to see currently stored recipes in a combo box.
I've recently been asked to sort the recipes so that they appear in the
combo box in alphabetical order. This is where I'm having problems and
I wondered whether anyone reading this can point me in the right
direction?

This is my script, when the form is initiated I'm getting a "Run-time
error 1004: Method 'range of object '_Global' failed.



Private Sub UserForm_Initialize()

Dim intNextRow As Integer
Dim strRowID As String
Dim strMixID As String
Dim LastRow As String
Dim WriteRow As String
Dim WriteRange As String

'On Error Resume Next

intNextRow = 4

Set ExcelAppRecipe = GetObject(, "excel.application") ' Set Reference
to currently open Excel Application
ExcelAppRecipe.Application.Sheets("Recipe").Activa te

LastRow = ExcelAppRecipe.Selection.SpecialCells(11).Row
WriteRow = "" & "5" & ":" & LastRow & ""
WriteRange = "" & "A" & LastRow & ""

ExcelAppRecipe.Rows(WriteRow).Select
ExcelAppRecipe.Range(WriteRange).Activate

ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending


strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Do While strMixID < ""
cboSelectMix.AddItem strMixID
intNextRow = intNextRow + 1
strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Loop
Set ExcelAppRecipe = Nothing
End Sub

I'm pretty sure it has something to do with the line
ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
but I'm not sute what I've done wrong? If I look on the excel sheet it
has selected the correct area but hasn't sorted it. Any ideas?

Thanks in advance

your are writing latebound code.

xlAscending is a constant defined in excel library
(which is not referenced).

thus= replace xlAscending with 1



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


marshy26 wrote :

Hi,
I have written a visual basic script that uses Excel to store 'recipe'
data. The user is able to see currently stored recipes in a combo box.
I've recently been asked to sort the recipes so that they appear in
the combo box in alphabetical order. This is where I'm having
problems and I wondered whether anyone reading this can point me in
the right direction?

This is my script, when the form is initiated I'm getting a "Run-time
error 1004: Method 'range of object '_Global' failed.



Private Sub UserForm_Initialize()

Dim intNextRow As Integer
Dim strRowID As String
Dim strMixID As String
Dim LastRow As String
Dim WriteRow As String
Dim WriteRange As String

'On Error Resume Next

intNextRow = 4

Set ExcelAppRecipe = GetObject(, "excel.application") ' Set Reference
to currently open Excel Application
ExcelAppRecipe.Application.Sheets("Recipe").Activa te

LastRow = ExcelAppRecipe.Selection.SpecialCells(11).Row
WriteRow = "" & "5" & ":" & LastRow & ""
WriteRange = "" & "A" & LastRow & ""

ExcelAppRecipe.Rows(WriteRow).Select
ExcelAppRecipe.Range(WriteRange).Activate

ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending


strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Do While strMixID < ""
cboSelectMix.AddItem strMixID
intNextRow = intNextRow + 1
strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Loop
Set ExcelAppRecipe = Nothing
End Sub

I'm pretty sure it has something to do with the line
ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
but I'm not sute what I've done wrong? If I look on the excel sheet it
has selected the correct area but hasn't sorted it. Any ideas?

Thanks in advance

Thanks for the replies. I've tried both solutions that have been
suggested but I'm still having problems?
What I have noticed is, if I create a macro in excel to do the sort and
then add the following line it works.

ExcelAppRecipe.Application.Run "'Theale SH Recipe.xls'!Sort_ID"

I don't really want to do it this way and would rather go down the
route i was going if it is possible?

I've done a few searches on the net and have tried various ways of
using the sort command but I'm still getting error 1004 :o(

Thanks once again

I was having the same problem trying to sort a data table that is stored in
an add-in. I think that William Benson is on the right track.

This worked for me...each time that you refer to the range, you need to
define the worksheet.

ExcelAppRecipe.Selection.Sort Key1:=ExcelAppRecipe.Range("B5"),
Order1:=xlAscending


"marshy26" wrote:

Thanks for the replies. I've tried both solutions that have been
suggested but I'm still having problems?
What I have noticed is, if I create a macro in excel to do the sort and
then add the following line it works.

ExcelAppRecipe.Application.Run "'Theale SH Recipe.xls'!Sort_ID"

I don't really want to do it this way and would rather go down the
route i was going if it is possible?

I've done a few searches on the net and have tried various ways of
using the sort command but I'm still getting error 1004 :o(

Thanks once again

Did you read KeepItCool's response? I thought he had already hit on the
real issue...
"keepITcool" wrote in message
oft.com...





your are writing latebound code.



xlAscending is a constant defined in excel library

(which is not referenced).



thus= replace xlAscending with 1







--

keepITcool

| www.XLsupport.com | keepITcool chello nl | amsterdam





"parr301" wrote in message
...
I was having the same problem trying to sort a data table that is stored in
an add-in. I think that William Benson is on the right track.

This worked for me...each time that you refer to the range, you need to
define the worksheet.

ExcelAppRecipe.Selection.Sort Key1:=ExcelAppRecipe.Range("B5"),
Order1:=xlAscending


"marshy26" wrote:

Thanks for the replies. I've tried both solutions that have been
suggested but I'm still having problems?
What I have noticed is, if I create a macro in excel to do the sort and
then add the following line it works.

ExcelAppRecipe.Application.Run "'Theale SH Recipe.xls'!Sort_ID"

I don't really want to do it this way and would rather go down the
route i was going if it is possible?

I've done a few searches on the net and have tried various ways of
using the sort command but I'm still getting error 1004 :o(

Thanks once again