VBA Frustration - Excel 1

[brianh123]

Hello:

Well I am trying to create an Excel Application, which would copy a selected (via a combo box) defined name to a new worksheet. I am having trouble.

Here is my current code:


code:--------------------------------------------------------------------------------
For r = 1 To nms.count
formation = cboSelectPlay.Text
nms(r).Name = formation
'wks.Cells(r, 2).Value = nms(r).Name
Debug.Print nms(r).Name
Debug.Print nms(r).RefersToRange.Address
Worksheets("Inglemoor LLF Playbook Template&quot.Range(nms(r).RefersToRange.Address).Copy
Set NewSheet = Worksheets.Add
NewSheet.Range("A1&quot.PasteSpecial Paste:=xlPasteAll
'wks.Cells(r, 3).Value = nms(r).RefersToRange.Address
Next
--------------------------------------------------------------------------------

 

[bryanbayfield]

I'm a little confused here.

Is this all of your code? It would seem not - the definition of what nms is is not included. It would seem to be a collection (of OLEObjects?) In any case, why would you need a collection object?

From your description of teh problem (and ignoring your code for teh time being I'd have thought something like the following would do the job:

Worksheets.Add
ActiveSheet.[a1].Value = ComboBox1.Value

(I put the code in the combobox1 event handler).

 

[bryanbayfield]

Ooops, I see that Geoff has responded to your duplicate message and understood it a bit better!

If you just wanted to copy the values from a named range selected via the combobox, then something like the following would do it:

sub combobox1_change()
dim copy_str as string, copy_var as variant
copy_str = combobox1.value
copy_var = range(copy_str).value
worksheets.add
activesheet.range([a1], activesheet.range(range(copy_str).rows.count, range(copy_str).columns.count)).value = copy_var

Can't check whether that would work at the moment - my copy of Excel is shafted!

 

[brianh123]

bryanbayfield - your code breaks, see below....

Here is the code that I was using:

Private Sub cboSelectPlay_Change()
Dim formation As String
Dim r As Integer
Dim nms
Dim wks
Set nms = ActiveWorkbook.Names
Set wks = Worksheets(1)
formation = cboSelectPlay.Text

'For r = 1 To nms.count
' formation = cboSelectPlay.Text
' nms(r).Name = formation
' 'wks.Cells(r, 2).Value = nms(r).Name
' Debug.Print nms(r).Name
' Debug.Print nms(r).RefersToRange.Address
' Worksheets("Inglemoor LLF Playbook Template&quot.Range(nms(r).RefersToRange.Address).Copy
' Set NewSheet = Worksheets.Add
' NewSheet.Range("A1&quot.PasteSpecial Paste:=xlPasteAll
' 'wks.Cells(r, 3).Value = nms(r).RefersToRange.Address
'Next

****below is code from bryanbayfield
Dim copy_str As String, copy_var As Variant
copy_str = cboSelectPlay.Value

////'it breaks on the line below, stating:
////Run time error '1004'
////Application-defined or object-defined error
copy_var = Range(copy_str).Value
Worksheets.Add

ActiveSheet.Range([a1], ActiveSheet.Range(Range(copy_str).Rows.count, Range(copy_str).Columns.count)).Value = copy_var

End Sub


Private Sub Worksheet_Activate()
Dim arr()
ReDim arr(0)
Dim i As Integer
Dim count As Integer
Dim lArr As String

'clear anything in the current combo box
cboSelectPlay.Clear

Set nms = ActiveWorkbook.Names

'build array
For r = 1 To nms.count
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = nms(r).Name & ";" & nms(r).RefersToRange.Address
Next

'att arra to combo box
i = 1
Do Until i = UBound(arr) + 1
count = InStr(1, arr(i), ";&quot
lArr = Left(arr(i), count - 1)
cboSelectPlay.AddItem (lArr)
i = i + 1
Loop
End Sub

 

[bryanbayfield]

Sorry about that. This code works:

Private Sub combobox1_change()
Dim copy_str As String, copy_var As Variant, copy_add As String
If ComboBox1 = "egrng" Then
copy_str = ComboBox1.Value
copy_var = Range(copy_str).Value
copy_add = Range(copy_str).Address
Worksheets.Add
ActiveSheet.Range(copy_add).Value = copy_var
End If
End Sub

Excel can be soooooooooooooooooooooooooooooooo fiddly with ranges! Oooooh, I could crush a grape!

 

[brianh123]

OK, these (copy_str) are defined names. I am now getting the error message:

Run time error '1004'
Method of 'Range' object '_worksheet' failed.

I don't get it, I click on help and it gives me a blank help page. I know that I am getting the correct value for the defined name copy_str.

 

[bryanbayfield]

That's very strange, I tested it with some dummy data and a combo box and it worked fine.

Will have to have a think about it - you must be doing something different from me.

 

[brianh123]

I posted the fole on the web (zipped):

http://www27.brinkster.com/hartmoon/llf/zipped/llf.zip

 

[bryanbayfield]

But setting the relevant range equal to teh array of values requires the string copy_add - did you copy my lastest routine exactly how it is above?

How about the string for the address of the named range - it should just be a range reference with no worksheet specified in it?

Are you looking at the different sheets in Excel as you're stepping through the routine? Long shot, I know but ...

Also Excel/ VBA help files - that's fairly typical bahviour I'm afraid!

 

[brianh123]

OK, first off I copied your code exactly as you had there. The file is posted as I said in the posting above. Please download it. The defined names are on seperate worksheets, I load them into a combo box. Upon a selection change I want to copy the range associated with the defined name into a new worksheet.

 

[brianh123]

Let's try that again:

http://www27.brinkster.com/hartmoon/llf/zipped/getfiles.asp

 

[brianh123]

One thing that I noticed is that the error message dictactes that I should have SP1 (via a google search). I do not have this (I am waiting on tech support to do it for me, I don't have the rights.) Do you think that could do it?

 

[bryanbayfield]

It's because your named ranges are in a different sheet to your combo box.

It needs a qualifier in front of it to tell it which sheet the range is in.

Shouldn't take too long to fix.

 

[bryanbayfield]

Private Sub cboSelectPlay_Change()
Dim copy_str As String, c As Long, r As Long
If cboSelectPlay <> &quot;&quot; Then
    copy_str = cboSelectPlay.Value
    Application.EnableEvents = False
    Sheet1.Select
    With ActiveSheet.Range(copy_str)
        c = .Columns.count
        r = .Rows.count
        .Copy
        End With
    Worksheets.Add
    ActiveSheet.[A1].Select
    With Selection
        .Resize(r, c).Select
        ActiveSheet.Paste
        .PasteSpecial Paste:=xlPasteFormats
        End With
    Sheet2.Select
    Application.EnableEvents = True
    End If
End Sub

"Works for me" as some bloke used to say on telly in a second-rate cop show.

Now excuse me, but I've had a stale turd of a day. I'm off to teh pub to drink some ale and pich some wench's bottoms.

Thanks you and goodnight.

 

[brianh123]

you da man!!!!! works great!!!! thanks..

 

[xlbo]

brian - seems to me that Bryan hepled you out a lot here and came up with a working solution. The standard way to say thatnks on Tek-Tips is to award a star. This can be done by clicking on the " Mark this post as a helpful/expert post" This is not only the recognised way of saying thanks but also aids people searching the archives so they can find similar quetions to their own and see where resolutions have been found Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[brianh123]

Done...Thanks!!!