Solved

VBA Excel - What cell does this refer to?

Posted on 2014-01-17

Microsoft Excel

3 solutions

497 Views

Last Modified: 2014-01-17

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

Comment

Question by:Patrick O'Dea

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

9 Comments

LVL 52

Accepted Solution

by:Rgonzo1971

Rgonzo1971 earned 300 total points

ID: 397881362014-01-17

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

LVL 3

Assisted Solution

by:Sreeram

Sreeram earned 100 total points

ID: 397881712014-01-17

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).

Author Comment

by:Patrick O'Dea

ID: 397882612014-01-17

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!

Author Comment

by:Patrick O'Dea

ID: 397882752014-01-17

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

LVL 27

Assisted Solution

by:MacroShadow

MacroShadow earned 100 total points

ID: 397882762014-01-17

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

LVL 52

Expert Comment

by:Rgonzo1971

ID: 397882802014-01-17

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

Author Comment

by:Patrick O'Dea

ID: 397883022014-01-17

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?

LVL 52

Expert Comment

by:Rgonzo1971

ID: 397883312014-01-17

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

Author Closing Comment

by:Patrick O'Dea

ID: 397883532014-01-17

Thanks ... I have made progress but not solved everything!

I will return to this later!

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding the Office Templates Folder

Article by: Helen

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …

Automating Microsoft Excel from Access (Part I)

Article by: Dale Fye

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…

Creating an Efficient Dashboard in Excel

Video by: Katie

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…