Worksheet Variable

[Xenocity]

HI,

I have tried looking at all the examples of how to create a formula in VBA that has the worksheet defined as a variable that I can easily change for each report I need.

I don't know how to define this at the top of my module.

Range("B486").Select
ActiveCell.FormulaR1C1 = _"=IF('2012master'!R[-483]C[82]="""","""",VLOOKUP(""Y"",'2012master'!R[-483]C[82]:R[-483]C108,24,FALSE))"

I am looking for something at the top similar to:

set ws = '2012master'

so that all the formulas on the vba will use the workshee that I define.

Secondly, I wanted to make the process easier for the user so that they could choose the worksheet from a user form to populate the specific worksheet being used for the variable if this is possible.

Thank you in advance.

 

[Ruddles]

Dim strSheetname As String
 
strSheetname="'[COLOR=red]2012master[/COLOR]'"
 
ActiveCell.FormulaR1C1 = _"=IF('" & [COLOR=red]strSheetname[/COLOR] & "'!R[-483]C[82]="""","""",VLOOKUP(""Y"",'" & [COLOR=red]strSheetname[/COLOR] & "'!R[-483]C[82]:R[-483]C108,24,FALSE))"

 

[Ruddles]

And to display a list of worksheets for the user to select one:-

Application.CommandBars("Workbook Tabs").ShowPopup

 

[Xenocity]

I am getting an application defined or object defined error

 

[Ruddles]

Soz - I accidentally copied the apostrophes. Try:-

strSheetname="[COLOR=red]2012master[/COLOR]"

 

[Xenocity]

That worked perfectly, thank you!

 

[Xenocity]

The first code worked great but I dont think I explained myself well enough for the second.

I would like the end user of my document to be able to select what worksheet becomes the variable without having to go into VBE and changing the strSheetname variable. (The VBA isn't run on the active worksheet, it is pulling information from the variable worksheet)

So esentially I'm wondering if I can create a user form or something to that nature that will automaticall fill the strSheetname variable.

 

[Ruddles]

Did you try this?

Application.CommandBars("Workbook Tabs").ShowPopup

(You can test it from the Immediate window.)

If you place that in a macro, you can call it from a button on your worksheet and that will change the active sheet.

Alternatively you can design a userform with just a single combobox on it, load the combobox with the names of all the worksheets in the workbook, then when the user clicks one of the names, store it in your variable and close the userform. (Or a listbox - whichever seems best.)

Assuming you use the names UserForm1 and ComboBox1, this should do the trick:-

Private Sub ComboBox1_Click()
 
  strSheetname = UserForm1.ComboBox1.Value

  Unload Me
 
End Sub
 
Private Sub UserForm_Activate()
 
  Dim ws As Worksheet
  
  UserForm1.ComboBox1.Clear
  For Each ws In ThisWorkbook.Worksheets
    UserForm1.ComboBox1.AddItem ws.Name
  Next ws
  
End Sub

That goes in the code module for the userform. Then all you need to do is UserForm1.Show, maybe from a command button on your worksheet.

 

[Xenocity]

That worked great! I can't thank you enough.

 

[Ruddles]

"I love it when a plan comes together."