Clearing Selected Cells On A Form

Oct 15, 2013

I have compiled the following code with bits from here there and everywhere to clear the user entered details on a form. My problem is that I need to retain or reset to "Please Select" the cells with drop down lists.

In list form, I am trying to;

Unprotect the worksheet,
Select the cells with drop down lists and lock them,
Delete the contents of unlocked cells,
Selecting and unlocking the cells with the drop down lists ready for re-use,
Re-protect the worksheet.

(I probably should mention that my "Form" is just a formatted worksheet that looks like a form on the screen and when printed, not any sort of inbuilt Excel function that I have read about somewhere, probably here)

Code:
Sub ClearUnlockedCells()
'
' Clearform Macro
' Clear entered data from Form.
'
Dim WorkRange As Range
Dim DVRange As Range

[code].....

View 4 Replies


ADVERTISEMENT

Clearing Form Controls?

Nov 18, 2009

I have two linked ComboBoxes on a form. There are 10 Textboxes populated by ComboBox2’s choice. These controls are situated on two frame controls which are used for visual groupings only. I would like to clear everything except Combo1 when Combo1’s value is changed. ( Combo1 is a filtered key list using the dictionary. Script code)

I’ve written a small sub that is called to clear the textboxes, but its not reliable every time. It seems that if the scroll bar is used, and the user chooses Combo1 choice “NUTS/SEEDS”, the boxes aren’t cleared. In fact it will show the “Walnuts” info in Combo2.

View 4 Replies View Related

Delete Content Form Selected Cells

Apr 15, 2014

i have data from D2:D10000

I Used data of first five rows e.g ("d2:d6") if in range ("d2:d6") any cell is blank then delete the all contents of the rows

e.g
d2 have data
d3 have data
d4, d5, d6 have no data then the contents of range ("d4:d6") should delete

View 3 Replies View Related

Clearing Sections Of A User Form

May 22, 2009

I have a user form with a button to clear the entire form on a click. I also have the form seperated into 2 frames, how do I set a button to clear only one of the two frames. Also how do I set a button to clear one text box on a frame? I know it must be simple, but I can't seem to figure it out.

View 3 Replies View Related

Populating Textbox From Combobox Options, Then Clearing Form

May 5, 2009

Error Help.xls. An example is attached. If you open the attachment, you can see that I'd like to choose a date from the combobox and have the corresponding "date code" (that's what I'm calling it - it's for a different, more complex purpose) appear in the text box. For this to happen, I'm using the following

View 2 Replies View Related

Clearing Form Control Checkboxes Automatically On Opening Workbook

May 8, 2009

I'm developing a workbook containing 121 sheets. Sheet 1 is effectively an index, and uses 120 form control checkboxes to unhide and display the selected sheets individually.

The code I'm using for each checkbox is below and is in a module.

View 8 Replies View Related

Clearing Unprotected Cells

Jul 19, 2002

I have a spreadsheet that has I have protected. On this sheet I have a button labeled Reset Form. When you click the button it it goes to each Unprotected Cell and deltes the contents. I created this by starting the macro and then tabbing to each and every cell and deleteing the contents I then ended the macro...

Is there a code that I can use for to clear all unprotected cells as opposed to creating the macro manually?

View 9 Replies View Related

Clearing Contents Of Cells

Apr 27, 2007

My computer runs on Excel 2003 but my Laptop is on Excel 2000, the problem is I have the below code on a spreadsheet I wrote on my Computer which runs fine but when I open it on my Laptop it comes up with a run time error it doesn’t like the clear contents line. If some one could explain to me why it is happening and how to get round the problem so it will work in Excel 2003 and 2000.

Private Sub Workbook_Open()
Application. ScreenUpdating = False
Sheets("data").Visible = xlVeryHidden
Sheets("Incidents").Visible = xlVeryHidden
Sheets("Front Sheet").Select
Range("A17:S17").Select
Selection.ClearContents
Open1.Show
Range("A17").Select
Application.ScreenUpdating = True
End Sub

View 2 Replies View Related

Clearing Cells Containing Specified Words

Apr 29, 2008

I am trying to clear the contents of cells in a very large spread sheet containing certain words. I am running the following macro and it will only do the first part, so it will clear cells containing "deceased" but stops at "esa". There may be any number of other words or text in the cell and I want it to delete cells with "esa" by itself so for ex. not delete cell containing "vanesa". I am very amateur so I am sure this is something simple but I cannot find the answer or figure it out for myself.

Sub DeleteAll()
Dim lCount As Long
Dim rFoundCell As Range
Dim rLookRange As Range
Set rFoundCell = Range("A1")
Set rLookRange = ActiveSheet.UsedRange
For lCount = 1 To WorksheetFunction. CountIf(rLookRange, "*deceased")
Set rFoundCell = rLookRange.Find(What:="deceased", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
rFoundCell.ClearContents.......................

View 6 Replies View Related

How To Stop From Clearing Hidden Cells

Aug 10, 2014

I have cells, or columns hidden when I do not need them, but I have to a data entered in those cells to make my spread sheet work. Usually just 0.

When I do the all clear or " clear all contents" operation at the end of the day, it clears the hidden data/cells too. Is there anything I can do to stop excel from clearing those hidden cells?

View 3 Replies View Related

Clearing Merged, Multiple Cells

Mar 25, 2009

I have a macro where somone fills in a form and then it copies the data in the form to a database (another worksheet). Once the macro is run and has copied the form to the database, I then clear each cell seperately using the below code :-

View 5 Replies View Related

Clearing Cells Based On Condition

Oct 3, 2008

I've created (with the help of jmthompson from here) a macro to pull an entire row of data and copy to another sheet (Sheet1) if column U has "YES" in the cell.

Now I need to clear the contents of cells in range B-U after the copy has occurred.

Here's the current macro: ...

View 9 Replies View Related

Clearing Contents Of Specific Cells

Dec 7, 2009

I created a macro to clear cells but I can't get it to clear a number of ranges. It will only clear single ranges.

Sub DeleteStuff()
Dim c As Range
For Each c In Range("G6:G10", "H6:H10", "J6:J10")
If c "" Then c.ClearContents
Next c
End Sub

View 9 Replies View Related

Populate Selected Row From Form

Jan 8, 2008

Thought it was better to start a new thread than to continue the old one since the focus in that thread was to get the values into a form and the question this tme is to get the values back into the correct row.

Background. My little application does the following...
In Form1 the user adds information that is inserted as a new row in my excel sheet. A unique ID is created and inserted in column A.
In Form2 the user sees a listbox with the rows in the excel sheet. The user then selects one of the rows and get all the information including the ID displayed to him/her in Form3.
In Form3 the user should then be able to update the values (except the ID) and then the values should be inserted back into the excel sheet in the correct row. I suppose I need the code behind my OKbutton to say something like

If Column A = value of IDBox, insert values in columnB, columnC etc...
But how do I write this code correctly?

View 9 Replies View Related

Clearing Unlocked Cells On A Protected Worksheet

Dec 6, 2007

I have a workbook with a series of sheets that have tables for entering data. The table row and column labels and formulas in certain cells of each sheet are locked and some sheets are hidden.

When opening the file I want to clear all cell contents (interior color, comments, data, etc) in all the unlocked cells on each sheet that is not hidden.

I tried protecting the sheets first so only the unlocked cells would be accessible, but when I run the code below I get an error saying that the clear contents etc. cannot be performed because the sheet is protected. If it is not protected, everything gets wiped out.

How can I keep the locked stuff but clear the unlocked cells?

For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then

ws.Protect Password:="aaa" 'Protect each sheet
ws.Cells.ClearContents 'clear content of any unprotected cells
ws.Cells.ClearComments 'clear any cell comments
ws.Cells.Interior.ColorIndex = 0 'set background colour to no fill
Active.Cells.Range ("a1") 'make the active cell the top left

End If
Next ws

View 9 Replies View Related

Extracting Selected Value In Form Listbox?

Nov 21, 2011

I have a userform running with a number of text boxes, as well as one listbox. To extract a value from a text box I obviously write something = me.textbox.value. How do I do the same for a listbox (single item) as me.listbox.value produces an error!

View 1 Replies View Related

Deleting / Clearing Multiple Cells In Target Range

Jun 2, 2014

I have a target range for a worksheet change. Then when finished I highlight the data and press 'delete' I get an error within the code.

View 2 Replies View Related

Print Selected & Multiple Rows To A Form

Jul 12, 2008

The file consists of 3 sheets:Orders,Order Form PO1 & Purchase Order. When I mark "x" in the row that I want to print, Order Form PO1 will be displayed with all data in the specific columns. However, it only allows me to select one row at a time. Now, I want to select multiple rows so that it will pass the data to the sheet Purchase Order.

View 13 Replies View Related

Display A Selected Area Of A Sheet In A VB Form

Jan 22, 2009

I need to display a selected area in an excel sheet in a VB form! I am not sure how to convert the selected area to an image ! The area already contains some images! I am attaching the file for ur refrence!

I need the portion which is within the frame to be converted as an image and get displayed in a Vb form!

View 3 Replies View Related

Open Data Form When Sheet Is Selected

Dec 13, 2006

I have a file with various tabs One of them is called "database"

What I would like is for the "DATA"/"FORM" to automatically open when the sheet is clicked on

View 9 Replies View Related

Store Variables Selected In List Box Form

Jun 13, 2008

how to store the variables selected in a list box for future use in the macro?

I've got code as follows:

This is in my main module

'These set up the global variables
Public SelectedRegion As Variant 'I've also used string
Public NewTabName As Variant 'I've also used string

'This links the list box to the values in a hidden tab named "Regions" and shows the form
WSForm.RgList.RowSource = "Regions!A1:A10"
WSForm.Show
This is in my coding for the form itself (list box & buttons)

'Code for my "Cancel" button
Private Sub cmdCancel_Click()
Unload Me
End Sub

View 9 Replies View Related

Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)

[Code]....

When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Last Selection In Multi Selected Form Control Listbox?

Jul 30, 2012

I have one form control listbox in excel. It is with multi selection Is there a way to find the latest selection in the listbox?

View 2 Replies View Related

User Input Returns Selected Form Output

Dec 29, 2013

Have a person Input an Assembly Number and,Have Excel return a list of Part Numbers, their Descriptions, and a Quantity for each Part Number that will need to be packaged with the Assembly Number that was Input.

The output needs to be in the form of a check sheet (I can play with the formatting if I can get the information out) for the one Assembly Number input.The output can either be in a separate spreadsheet or a User Form. The spreadsheet is easier for me to work with but the User Form looks cooler.

I can use a VBA Function to Input the Assembly Number but then I need some way of searching a small table (about 20x20 cells) to find the matching Assembly Number and then return the Part Numbers, etc. to a check sheet so the parts can be packaged. Each Assembly Number can have several Part Numbers associated with it. Either unique to that Assembly Number or common to other Assembly Numbers. The Quantities can also be unique or common. A couple of simple examples might be:

Assembly Number - ANA0134
P/N Description Qty.
ANA0118 Widget thing 2
ANB0554 Thing Widget 4

Assembly Number - ANA0431
P/N Description Qty.
ANA0118 Widget thing 3
ANB0554 Thing Widget 4
ANC0023 Plain Widget 1

View 4 Replies View Related

How To Take Excel Data From Selected Row And Paste It To Form In Another Worksheet

Apr 30, 2014

I have an Excel worksheet with potentially 1000+ rows of data.

Create a way where I select something (a macro button ?) for that individual row of Worksheet1 -- and it takes selected cell data from that row and inserts it into specified cells in another worksheet (Worksheet2) within that same workbook.

Also, this Excel data (of Worksheet1) will be sorted every week - so the ?macro button? that is used needs to follow the same row when a sort is performed.

I can do this by creating a macro button, but would have to create an individual macro button for every individual row of data. (That's 1000+ macro buttons to create !?!)

Is there better way of doing this with a macro button OR is there a completely different/better way to accomplish the same thing ?

View 2 Replies View Related

Have The First Value From Combobox Selected Every Time The Form Loads/opens

Jun 2, 2006

I have a combobox with populated values from a named range. I need the first value to be always selected when the form is opened - is there a way to do this

View 7 Replies View Related

Order Form (worksheet) Populated Sequentially With Items Selected From Other Worksheets?

Feb 6, 2013

I have a workbook with a series of worksheets with stock lists and pricing that I would like a user to be able to select items on (say, with a checkbox) that would then automatically populate a separate master Order Form sheet. The Order Form sheet is currently blank, with headers, and I would like only those items selected on the various stock sheets to be displayed on the Order Form.

View 4 Replies View Related

Copy Rows To New Sheet Based On Date Range Selected On A Form

May 8, 2009

This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet:

With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.

Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.

I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.

View 7 Replies View Related

Run A Macro Called "PriceForm" Instead Of Clearing The Cells

May 30, 2009

Code to run a macro called "PriceForm" instead of clearing the cells.

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("T5:X50").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

View 9 Replies View Related

Clearing Of The Cells Clears As Well The Copying Of "B30"

Oct 14, 2008

Sub test()
Range("B30").Copy

Range("A30:B30").ClearContents

End Sub
Problem: the clearing of the cells clears as well the copying of "B30"

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved