Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

VBA Excel - What cell does this refer to?

Hi,
I have some moderate knowledge of VBA - not great tho!

I am trying to fix something for a colleague.
At this moment, I cannot upload the sheet onto EE.

Specifically, I need help with the following line;

If (Sheet1.DTBox2.Value - Sheet1.DTBox.Value < 0) Then

I understand that it is comparing two dates etc.
However, where do I find the value for Sheet1.STBox2.value ?

1. Should I be looking in some cell on sheet1?
2. Should I be looking in the "Names Manager"?
3. The workbook is fairly complex and perhaps there are external links
0
Patrick O'Dea
Asked:
Patrick O'Dea
3 Solutions
 
Rgonzo1971Commented:
Hi,

Look for a TextBox Control

You can use this to see the names of all shapes ( and controls )

This should help
Sub macro()
For Each sht In ActiveWorkbook.Sheets
    sht.Activate
    For Each shp In sht.Shapes
        shp.Select
        MsgBox shp.Name
   Next
Next
End Sub

Open in new window

Regards
0
 
SreeramCommented:
Hi

 its refering to a  control in the sheet1.

To check the control:

1.Select Page Layout in the menubar.
2.under Arrange group.
3.Select selection pane (it will provide you all the controls used in the sheet).
0
 
Patrick O'DeaAuthor Commented:
Great ... making progress and found the text box.

However, the textbox has a value of =EMBED("MSComCtl2.DTPicker.2","")


This is obviously referring to a date somewhere.

I am unfamiliar with "EMBED" ..... can you explain
 =EMBED("MSComCtl2.DTPicker.2","")

Thanks again!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Patrick O'DeaAuthor Commented:
Thanks folks,


.... making progress!

I have found the textbox and it has a value of

=EMBED("MSComCtl2.DTPicker.2","")

Next question!  What does this mean??  It obviously points to a date field ... but I am unfamiliar with EMBED
0
 
MacroShadowCommented:
Ignore it, your are not meant to see it at all, it's an internal excel function. stackoverflow.com/questions/9373348/what-does-embed-function-mean-in-excel-controls
0
 
Rgonzo1971Commented:
HI,

=EMBED("MSComCtl2.DTPicker.2","")

is not is value but the control itself the value is what you should see in it a date because it's a datepicker control
the value is extracted in vba with the "DTBox.Value" part

Regards
0
 
Patrick O'DeaAuthor Commented:
Rgonzo,

Thanks but a final query?

the value is extracted in vba with the "DTBox.Value" part


I am sure you are correct but how does "DTBox.Value" relate to =EMBED("MSComCtl2.DTPicker.2","")

Perhaps this is too difficult to answer without displaying the actual spreadsheet.

What do you think?
0
 
Rgonzo1971Commented:
Hi,

DT Box is the name of the control

Here with an example wit another Control named TextBox2

And this snippet
Sub macro()
MsgBox Sheet1.TextBox2.Name & ": " & Sheet1.TextBox2.Value
End Sub

Open in new window

Regards
ControlTest.xlsm
0
 
Patrick O'DeaAuthor Commented:
Thanks ... I have made progress but not solved everything!

I will return to this later!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now