Keep/Retain Values Of Dtpicker Controls & Read Back

Sep 9, 2006

In a userform I have 6 DTPlcker controls for 3 periods ( X3). The dates chosen are not saved unfortunately and once I exit and retrieve the file again all the dates in the 6 DTpickers get the date of today.

View 7 Replies


Retain Values Of UserForm Controls Between Sessions

Aug 28, 2006

how you can save the status of a userform.

I have a userform that have a series of checkboxes and i want to know if i can save the status the checkboxes when i exit the userform.

View 5 Replies View Related

How To Read And Set Cell Back Color In VBA

May 24, 2013

How to read and set a cell backcolor in VBA ?

I've tried all sorts of things ( interior. colour, interior.colorIndex) but nothing gets the correct color.

View 5 Replies View Related

Retain 2003 Code In 2007 And Back To 2003

Aug 29, 2008

I've created a workbook with code to email it to a reviewer then back to the originator when reviewed (along with other editing functions etc.). The problem - The originator is working in Excel 2003, the reviewer in 2007. The macros work great until the reviewer sends it back. I have not been able to figure out which FileFormat:= ???? to use when I SaveAs prior to emailing as an attachment. My Excel 2003 doesn't recognize the .xlsm file and other formats are eliminated the vba/macro coding

View 4 Replies View Related

Saving A Back-up File As "Read Only"

Mar 18, 2008

I am using XP and Excel 2003 and I have a workbook that I currently back up to a remote server whenever closing out the book. This back-up copy becomes a "controlled document" that should not be used or changed once it is created. Is there anyway to make it save as a "Read Only" file, so that no one can modify the back-up copy.

Currently I do it manually, but that is becoming tiresome as well as when I forget that nice little error message pops up and....

This is my code so far:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
Dim Ans As Integer
Dim FName As String
Msg = "Would you like to make a back-up of this file?"
Ans = MsgBox(Msg, vbYesNo, Title)
Title = "Team Facilities"
If Ans = vbYes Then
FName = "P:Facilities Departmentackups2007" & ThisWorkbook.Name
ThisWorkbook.SaveCopyAs FName
End If
End Sub

View 9 Replies View Related

How To Retain Leading Zeros In A Cell When Combine The Values Of Two Cells?

Oct 25, 2012

I have managed to retain leading zeros in a cell by formatting the cell and selecting Number and Custom and adding the maximum number of zeros I want to have in front of the number in the cell i.e. 000002.

I want to have another cell in the spread sheet which contains the value 35394000002 using the formula =A24&B24.

When I use this formula I get 353942. How can I retain these zeros?

View 1 Replies View Related

Dtpicker In Multipage

Mar 7, 2007

In a Multipage form, the first page contains 2 DTPickers. These show the date value from the specified excel worksheet cells and update the cells accordingly if the user changes the dates with the TDPicker. These work perfectly.

If I place another DTPicker control on another page of the Multipage though, it leads to a run-time error.

As an example, the code for 1 of the successful DTPickers on the first page:

Private Sub DTPickerAccountsStartDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub

Private Sub UserForm_Initialize()
Application. ScreenUpdating = False
DTPickerAccountsStartDate.Value = Format(Range("B6").Value, "dd/MM/yyyy")
End Sub

There are no errors in any of the cell references etc and the formatting of the DTPicker controls is exactly the same in all cases.

View 6 Replies View Related

Set DTpicker To Todays Date

Dec 17, 2007

My datepicker automatically defaults to the date when it was first set up. I would like it to always show the current date when the user form is first opened...

View 6 Replies View Related

Merging MonthView & DTPicker Value Into One Cell

Sep 1, 2009

I have a user form on the attached example and on the form i have 'Month View' calendar control and 'Date/Time Picker'.

I am trying to merge the valuses of both controls into one cell. I have tried doing this with the following

View 9 Replies View Related

DTPicker: Redo The Same Thing Over And Over Again When Others Opens

Feb 21, 2007

i implement DTPicker inside a userform in my pc ... but when i open the userform in other pc there's an error saying could not found DTPicker ... when i reopen it in the pc that i've made the userform the DTPicker is no longer there and i had to redo the same thing over and over again when others opens it in other pc.

View 4 Replies View Related

DTpicker Value To Cell From Multipage Control

Sep 22, 2007

I have a DTPicker on a multipage form on a userform with the following codes:

sheet1. range("Ac2").value=DTPicker1.value


The first line of code is in the DTPicker1_change event.

The second line of code is in the userform_initialize event.

This works fine if I move the DTPicker control onto the userform itself, but if I put the control on the multipage form it fails with the following message:
Run Time Error 35788

An error occurred in a call to the Windows date & time picker control.

View 4 Replies View Related

VBA - Read Values Of Many Cells

Feb 25, 2009

Excel 2003 > I am developing a comprehensive VBA function that needs to read the value of many cells in a worksheet. I don't want to Name all those cells because then I will need to pass too many variables to the function. I can use the following set of objects and properties ...

View 6 Replies View Related

Read Filter Values

Sep 3, 2009

I am using a OLAP cube, and i dragged a group dimension as Report filter,

I am using pivotitems and looping , but not able to read the filter values..
other than pagefields.cubefield.currentpage

we have around 200 groups, but we have to display only groups belonging to user. so i have macro which gets the list of just user groups..
so need to delete/update filter items so only user can see just see their groups.

1. In VBA code, i am not able to read the values , i am able to read pagefield.currentpagename but not all the other values

2.There are 200 values in the filter, based on other list -- i need to loop and delete some of these values from filter

View 11 Replies View Related

Read Numeric Values VBA

Aug 13, 2012

I have the following code and I am really confused to, why its not reading numeric values from the code below:

Sub MG08Aug32()
Dim Rng As Range
Dim Dn As Range
Dim n As Integer
Dim Num As String
Set Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))


I desire the following output below:





View 6 Replies View Related

VBA Does Not Read DDE Feed Values

Dec 2, 2012

Have a dde feed coming in from an external source. I also have a VBA written below to play sound whenever the feed goes below a certain level. When I key in values manually through the function bar the sounds play perfectly. But for some reason it is not reading the DDE feeds as value and it can go past my level without playing the sound.

Option Explicit
Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long


View 5 Replies View Related

Searches Through A Range And Changes Back Color To Values

Feb 11, 2014

I am doing a spread sheet for participation in a class. What I want is for whenever a student is absent, i.e. has a 0 in their point box for the day, that cell turns red. I have tried to make the .find method work but it has been uncooperative and so far and I can't seem to get it to even run. This is what I have so far:

HTML Code: 

Private Sub For_Loop_Click()
Set v = .Find(0, LookIn:=xlValues)
For Each v In [B6:B46]
If v.Value = 0 Then v.Interior.ColorIndext = 3
Set c = .FindNext(c)

End If
End With
End Sub

View 2 Replies View Related

Read Some Values From A Closed Workbook

May 4, 2009

I am trying to read some values from a closed workbook. I use the following code

View 3 Replies View Related

Possible To Read Values From Closed Workbooks?

Nov 6, 2007

I'm wondering if it is possible to read values from different workbooks and not open them first.

I have an overview sheet with values from the other workbooks, and I will not use relative references but us a macro that runs trough the workbooks and collect the values.

View 9 Replies View Related

Read Values From One Workbook Into An Array

Jul 7, 2006

I am trying to create a "macro" that will read values from one workbook into an array based on a key field. If the key isn't present the cells data is not to be stored. After the records are stored i want to test them agains a range in a differnt workbook. If the data compared matches a stored value, leave it alone. If there is no match, a cell needs to be added with a value stating a differnce.

Like i stated i am new at this and have no clue how to compare an data array to a field. Let alone read the values into the array.

View 12 Replies View Related

Duplicate Values For VLookup - How To Receive Data Back

May 23, 2014

I have a situation where I have about 20000 lines to populate and looking for a quicker alternative. I have duplicate numerical values in the lookup_value. I want the same number to appear for all lookup_value's but there is only one instance of that value on the other sheet I am searching. My table looks like this:




Column A Column B

50058459 1234
68594523 9876
58965214 3456

I want my output of vlookup to look like this

Column A Column B

50058459 1234
50058459 1234
50058459 1234
50058459 1234
68594523 9876
68594523 9876
68594523 9876
58965214 3456

View 7 Replies View Related

ComboBox List To Read Values From An Array

May 7, 2008

How do I set a ComboBox list to read values from an array? I tried the following in VBA, but it didn't work....

ComboBox1.List = Array("January", "February", "March", “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”)

View 9 Replies View Related

Read Many Cell Values From Closed Workbook

Apr 16, 2009

I've recently discovered the usefulness of VB in excel and have managed to write some basic macros that enhance many of my workbooks. BUT, I've bumped into a problem I can't seem to solve. Please help!

I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory. I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?

I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance:

Problem is, I'm so new that I don't even know where to begin pasting the code (objects vs. modules, etc) in the VBA editor.

View 9 Replies View Related

Search Criteria Bring Values Back To A Results Column

Apr 25, 2008

i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results

LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row
Application.ScreenUpdating = False
For i = 26 To LastRow
Target = Sheets("design owb").Cells(i, 10)
With Sheets("design")
For J = 2 To 54
For K = 4 To 11
If Target = .Cells(J, K) Then
Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | "
End If
Next K
Next J
End With
Next i
Application.ScreenUpdating = True

View 9 Replies View Related

Calculating Distances Between Back-to-back Locations

Aug 11, 2012

I have a layout something like the following:



Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).

Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".

Ultimately I am looking to have a matrix which contains all the distances between each location:



View 4 Replies View Related

File May Be Read-Only, Or You May Be Trying To Access A Read-Only Location

Jan 4, 2007

I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.

Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:

- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.

This file is dated back in you think it's corrupt? Is there anything i can do to open or recover this?

View 2 Replies View Related

Create A Reset Button That Returns Combo Box Values Back To The First One In The List And Clears All Cells At The Same Time

Nov 19, 2009

I have 4 combo boxes with selectable options and several cells for inputting data into. I'm looking to create a reset button that returns combo box values back to the first one in the list and clears all cells at the same time. I've figured out the cell reset as follows;

View 3 Replies View Related

Manage "empty" Dates From DTPicker Control

May 20, 2006

I'm having a problem with a form where I have created several DatePicker controls.
Once the user select the control and select a date, it's saved on a cell (all the data is stored as a record, including dates). However, the user could not select a date, and, in this case, I should record nothing on that cell. What I have done is to enable the checkbox property.. so I assumed that the date is valid if this checkbox is checked (however, I don't know how to validate if this checkbox is checked or not). The checkbox property for the date picker is a yes/no value that just show the control as enabled/disabled whether you check or not that checkbox.

Question I have is: How can I do to see if I should be saving a date or not on the cell? I have tried different formulas without success. Sometime works and sometime no. I'm using the following code to validate if the user has selected a date:

If IsNull(DTPicker18.Value) = False Then
Worksheets("DAT").Cells(nPubRow, 37).Value = DTPicker18.Value
' If the user deselected the checkbox I should delete the cell content...
Worksheets("DAT").Cells(nPubRow, 37).Delete
End If

What happens is that sometimes this condition returns True.. but others it returns a 0:0:00 value that is not null.

View 2 Replies View Related

Values In Each Cells(A) Represented Back At Cells(B) But No Repetition If Some Cells(A) Contains Same Value

Dec 9, 2008

I did my search, but cant find and knows what key search to look/type for...

If i have data A1 through A10, such as 1 1 2 2 2 2 3 3 3 3

How can i get column B1 through B3 as 1 2 3 ?

View 9 Replies View Related

Userform Will Not Retain Changes

Sep 20, 2006

I am building a large Userform and I am going back to edit some of the controls (renaming, resizing, etc). The problem is, after editing, the changes don't stick. I can open another module and then open the form again and the changes will be gone. I can save Excel, close the file, and open it again, and the changes will be gone.

View 4 Replies View Related

Retain Formula In Cell

Feb 4, 2009

Is it possible to retain the formula in particular cells within an excel spreadsheet whilst clearing the rest of the sheet, and if so how?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved