Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reference a worksheet name that can change 2

Status
Not open for further replies.

Pudsters

Technical User
Mar 16, 2006
151
US


I need to reference few ranges on a worksheet named Invoice (Invoice), but sometimes its name changes to Invoice 1

How would I allow for either scenario?

Worksheets("Invoice").Range("A1") or Worksheets("Invoice 1").Range("A1")

I tried something like this thinking Invoice is the codename whether it is Invoice or Invoice 1, but it didn't work:

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Invoice")

ws(Invoice).Range("A1")​

 
Hi,

Under what circumstances does the sheet name change?

Does the sheet name ALWAYS begin with Invoice?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 

Yes it always begins with Invoice. The workbook allows the user to open up new invoices and other named worksheets. If they open up new Invoice worksheets, Invoice changes to Invoice 1, and each new invoice becomes Invoice 2, Invoice 3 and so on. I only need to reference the MAIN invoice, which will be either Invoice or Invoice 1.
 
please post the entire code that you're using.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
It's actually based on my last post:

Code:
Sub sendInvoice()

    Dim i As Integer
    Dim ws As Worksheet
        
    'Set ws = ThisWorkbook.Sheets("Sheet1") - This didn't work!
    
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        
        .Navigate "[URL unfurl="true"]http://website.com"[/URL] 'change name!
        
        
        Do
            DoEvents
        Loop Until .ReadyState = 4     
         
        With .Document
            For i = 1 To 9
                If Not .getElementById("checkboxIdj" & i).Checked Then
                
                    .getElementById("checkboxIdj" & i).Checked = True                    
                    .getElementById("invoice-j" & i).Value = Worksheets("Invoice").Range("A1")
                    .getElementById("customername-j" & i).Value = Worksheets("Invoice")."B1")
                    
                                     
                    Exit Sub
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With
 
Code:
Private Sub sendtimecard()
    Dim i As Integer
    Dim ws As Worksheet, [b]wsINV As Worksheet[/b]
        
    [b]For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Set wsINV = ws
                Exit For
        End Select
    Next[/b]
    
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .Navigate "[URL unfurl="true"]http://website.html"[/URL]
        
        Do
            DoEvents
        Loop Until .ReadyState = 4
        
        With .Document
            For i = 1 To 3
                If Not .getElementById("chkboxJOB" & i).Checked Then
                
                    .getElementById("chkboxJOB" & i).Checked = True
                    .getElementById("txtNameJOB" & i).Value = [b]wsINV[/b].Range("A1")
                    .getElementById("txtHoursJOB" & i).Value = [b]wsINV[/b].Range("B1")

                    GoTo Xit
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With
    
Xit:
    Set wsINV = Nothing
End Sub

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 

Is it always the first (second? third?) worksheet in your workbook?

If [blue]second[/blue] (for example), you may try:

Code:
Set ws = ThisWorkbook.Sheets([blue]2[/blue])

---- Andy

There is a great need for a sarcasm font.
 


It is always the FIRST worksheet!

I get "Object Variable or With Block Variable not set" error at this line:

.getElementById("txtNameJOB" & i).Value = wsINV.Range("A1")
 
Please post your entire code.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
My code is now showing named ranges, but I had tried it with just ("A1") and ("B1")as well.
Code:
Sub sendtimecard()
On Error GoTo Err_sendtimecard

    Dim i As Integer
    Dim ws As Worksheet, wsINV As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Exit For
        End Select
    Next
       
    With CreateObject("InternetExplorer.Application")
        .Visible = True
               
        .Navigate "[URL unfurl="true"]www.webpage.com"[/URL] 'THIS IS LOCAL TEST SITE ONLY!
               
        Do
            DoEvents
        Loop Until .ReadyState = 4     
              
        With .Document
            For i = 1 To 9
                If Not .getElementById("chkboxJOB" & i).Checked Then
                
                    .getElementById("chkboxJOB" & i).Checked = True
                    .getElementById(txtNameJOB" & i).Value = wsINV.Range(RANGE_CUSTNAME)
                    .getElementById("invoiceJOB" & i).Value = wsINV.Range(RANGE_INVOICENUMBER)
                                     
                    Exit Sub
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With

    
Exit_sendtimecard:
    Exit Sub
    
Err_sendtimecard:
    MsgBox Err.Description
    Resume Exit_sendtimecard
    
Set ws = Nothing
    
End Sub
 
Put QUOTES around RANGE_CUSTNAME and RANGE_INVOICENUMBER

Or...
Code:
‘
                    .getElementById(txtNameJOB" & i).Value = [RANGE_CUSTNAME]
                    .getElementById("invoiceJOB" & i).Value = [RANGE_INVOICENUMBER]
Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 

Quotes don't work, I tried your 2nd option with the brackets, but it returns the cell reference instead of the cell value
 
Code:
'
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                [highlight #FCE94F]Set wsINV = ws[/highlight]
                Exit For
        End Select
    Next

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 

I don't know how that wasn't showing up above, but it is in my code. Sorry about that, still getting that same error message: Object Variable or With Block Variable not set

So far, it only works if I use:

.Value = Worksheets("Invoice").Range(RANGE_INVOICENUMBER) or: .Value = Worksheets("Invoice 1").Range(RANGE_INVOICENUMBER)
 
Please post your code again.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Here it is, with the code that works for single Invoice commented out:

Code:
Sub sendtimecard()
On Error GoTo Err_sendtimecard

    Dim i As Integer
    Dim ws As Worksheet, wsINV As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Invoice", "Invoice1", "Invoice 1"
                Set wsINV = ws
                Exit For
        End Select
    Next
       
    With CreateObject("InternetExplorer.Application")
        .Visible = True
        
        .Navigate "[URL unfurl="true"]http://www.test.com"[/URL] 'THIS IS LOCAL TEST SITE ONLY!
               
        Do
            DoEvents
        Loop Until .ReadyState = 4   
       
        
        With .Document
            For i = 1 To 9
                If Not .getElementById("checkboxIdj" & i).Checked Then
                
                    .getElementById("chkboxJOB" & i).Checked = True
                    .getElementById(txtNameJOB" & i).Value = wsINV.Range(RANGE_CUSTNAME)
                    .getElementById("invoiceJOB" & i).Value = wsINV.Range(RANGE_INVOICENUMBER)

                    
'                    .getElementById("txtNameJOB" & i).Value = Worksheets("Invoice").Range(RANGE_CUSTNAME)
'                    .getElementById("invoiceJOB" & i).Value = Worksheets("Invoice").Range(RANGE_INVOICENUMBER)

                                     
                    Exit Sub
                End If
            Next
        
            MsgBox "Timecard is full!"

        End With
    End With

    
Exit_sendtimecard:
    Exit Sub
    
Err_sendtimecard:
    MsgBox Err.Description
    Resume Exit_sendtimecard
    
Set ws = Nothing
    
End Sub
 
Your code is missing a QUOTE
Code:
 .getElementById([b][highlight #FCE94F]"[/highlight][/b]txtNameJOB" & i).Value = wsINV.Range(RANGE_CUSTNAME)

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 

Yeah, I made an edit in the post, it is there in my vba though!
 
Well....???

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
I made an edit in the post" - you cannot do that, please.

When Skip asks you to "Please post your code ", please - Copy your code and Paste it in here.
Do not edit anything.

Otherwise we will play a guessing game with no end.


---- Andy

There is a great need for a sarcasm font.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top