Excel macro that references a different worksheet

I've attached an Excel file that currently only uses 1 sheet (Sheet1).   If you look all the way to the right in Sheet1, you'll see data in columns XFA through XFD.   The two comboboxes and single textbox on Sheet1 reference that data.  

All I would like to do is have the code under the textbox in Sheet1 reference columns A through D of Sheet1A rather than XFA through XFD of Sheet1.   I tried modifying it myself and I can't get it to work.

In other words, what I'm looking to do is replace Sheet1's use of columns XFA through XFD with columns A through D of Sheet1A.  What I did so far was to create a new sheet (Sheet1A.  My original file only had one sheet) and copied the data from XFA through XFD of Sheet1 to A through D of Sheet1A.  I'm now trying to modify Sheet1's textbox code (on row 3 starting in column C) to reference Sheet1A instead of Sheet1.  The version of the file I attached does not have the comboboxes updated with the correct LinkedCells and ListFillRanges although I do have those working on my end with a different version.  For example, the LinkedCell of the leftmost combobox is: Sheet1A!B1

Thank you
Test-file-for-EE.xlsm
dbfromnewjerseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
All you should need to do is change the LinkedCell and ListFillRange properties of the comboboxes and textbox so they refer to the correct sheet and ranges.

For example the LinkedCell for ComboBox1 should be set to Sheet1A!B1 and the ListFillRange set to Sheet1A!D1:D12.

Are you having problems doing that?
0
dbfromnewjerseyAuthor Commented:
I tried that already and it's not working.  If you look at the code under the textbox (which I did not author), if references cells on Sheet1.  I'm thinking that code needs to be changed to reference Sheet1A but so far, have not been able to get it to work.

To clarify... the comboboxes work fine. It's the textbox that's not working. The textbox action is driven by code which needs to be modified because of the addition of the new sheet.
0
dbfromnewjerseyAuthor Commented:
Below the single line of code three lines down is the existing code under the textbox from when the file only contained one sheet.  I put a comment on each of the two lines I think need to be modified.   For one of the lines that I believe needs to be modified, I've already tried something like the following and it's not working:

dateSelected = DateSerial(ws.Range("Sheet1A!C1").Value, ws.Range("Sheet1A!A1").Value, 1)  
 
This is the existing code under the textbox when it was set up as a single worksheet file:

Private Sub TextBox1_Change()

   Dim dateSelected As Date
   Dim dateCurrent As Date
   Dim wb As Excel.Workbook
   Dim ws As Excel.Worksheet

   Set wb = ThisWorkbook
   Set ws = wb.Worksheets("Sheet1")                                                                                      <---- I think this needs to be modified
       dateSelected = DateSerial(ws.Range("XFC1").Value, ws.Range("XFA1").Value, 1)     <----I think this needs to be modified
       dateCurrent = DateSerial(Year(Now), Month(Now), 1)
   
   If (dateSelected >= dateCurrent) Then
       TextBox1.Text = "Invalid period selected. Report will not be accepted until corrected."
       TextBox1.BackColor = RGB(255, 255, 0)
   Else
       TextBox1.Text = ""
       TextBox1.BackColor = RGB(255, 255, 255)
   End If
   
   Set ws = Nothing
   Set wb = Nothing

End Sub
0
NorieAnalyst Assistant Commented:
Why are you using Textbox1's change event?

Aren't you trying to check that the values selected in the comboboxes constitute a valid selection?

If you are then you should probably be looking at using the change event(s) of the comboboxes.

Have a look at the attached file and see if it does what you want.
Test-file-for-EE.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbfromnewjerseyAuthor Commented:
Thank you very much. That works.  To answer your question...someone else gave me that code for the textbox.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.