Solved

VBA Excel - What cell does this refer to?

Posted on 2014-01-17
9
460 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
0
Comment
Question by:Patrick O'Dea
9 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 39788136
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
 
LVL 3

Assisted Solution

by:Sreeram
Sreeram earned 100 total points
ID: 39788171
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
 

Author Comment

by:Patrick O'Dea
ID: 39788261
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
 

Author Comment

by:Patrick O'Dea
ID: 39788275
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 100 total points
ID: 39788276
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39788280
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
 

Author Comment

by:Patrick O'Dea
ID: 39788302
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39788331
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
 

Author Closing Comment

by:Patrick O'Dea
ID: 39788353
Thanks ... I have made progress but not solved everything!

I will return to this later!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
consolidate from multiple folders 5 39
Macro Capitalize 4 51
Excel 2016 Not Responding Issues 6 22
Manually enter date in datepicker 24 28
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now