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?
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
dbfromnewjerseyAuthor Commented:
Thank you very much. That works.  To answer your question...someone else gave me that code for the textbox.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.