How to add variables for Formula Names in VBA

[Brad Birdman]

Good day,
I consider myself an amateur to basic user of VBA but can learn quick. I have found many answers and help on this site and appreciate this site very much.

I work in Excel 2010 on Windows 7 OS. </SPAN>

My current project is the client that wants to have a message box come up when a certain cell is clicked. Then a user form come up to input data, then put that data on the spreadsheet. I got that part done and working. The spreadsheet though has the ability to add additional rows if more space is needed with in the table to put data. </SPAN>

What I did for the part if a certain cell is picked is the following:</SPAN>

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)</SPAN>
If Target.Column = 2 And (Target.Row >= 48 And Target.Row <= 98) Then</SPAN>
If Y = "Yes" Then Exit Sub</SPAN>
Dim RCV As Integer</SPAN>
RCV = MsgBox("Has the verification of room classes on the AR100 been completed for the month?", vbYesNo + vbQuestion, "GSS 3 Verification Box")</SPAN>
Select Case RCV</SPAN>
Case vbYes</SPAN>
Verificationbox.Show</SPAN>
Case vbNo</SPAN>
MsgBox "This message will appear on the 15th through the end of the month until completed. See job aid for detailed instructions.", vbCritical, "GSS 3 Verification Box"</SPAN>
End Select</SPAN>
End If</SPAN>
End Sub</SPAN>


This works great, but when more rows are added then my target rows then become messed up. The spreadsheet tracks data for the month. So each day of the month has 3 rows. So the first day of the month has rows 1, 2, and 3, and with the option of adding more rows. The client wants the message box to come up on the 15^th</SPAN> of the month, which starts at row 48 till the end of the month which is cell 98. When more rows are added my 48 then goes into the days before the 15^th</SPAN>. I tried using the $ for relative reference but it is not working. I then figured that using names, like the formula names, it will then find the right row and place the information in the correct cell due to the $ working for relative reference. </SPAN>

However I have a worksheet for every month. Creating all the names is not the issue, but getting all the monthly names to work with the part of the code Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range). Now this is where my knowledge ends. That type of code I think is not considered a sheet code but a global code if that is correct terminology. </SPAN>

My question is how do I get the different sheet names to work with that part of code that is in the workbook in the sheet selection change? How do I get a variable to be in front of the cell name? So I Dim Y as Range, Set Y = THisworkbook.names(“name1”).referstorange. But since “name1” will be used on every sheet like Januaryname1, Februaryname1, Marchname1, and so on. How do I get a variable in front of the (“name1”) part so that it will take the active sheets name of the Month, and put that in front of the cell name range of “name1”? I thought of setting up a variable as M for month and somehow putting that infront of the Thisworkbook.names(“name1”).referstorange some how but I am not sure how. </SPAN>

I get in trouble for making long winded messages, documents, and emails, so I am sorry for the long document. I feel that explanation and details are important. I hope this makes sense as this stuff can be hard to explain. Sadly, the workbook cannot be uploaded due to protected health information but I can do parts of code if more is needed. </SPAN>

 

[ryansclev]

How do I get a variable in front of the (“name1”) part so that it will take the active sheets name of the Month, and put that in front of the cell name range of “name1”?

Do you mean something like this?

Dim strNameRange as string
strNameRange = sh.Name & "name1"

 

[Brad Birdman]

I figured this out by going through another method. Thanks for help, views, and such a great site. Issue resolved. I landed up using a static range that always has a number in it for days instead of names so it works on any sheet. I tried copying the code into the thread but would not let me.