Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Isolate Rows From Formulas

Posted on 2014-01-04
2
Medium Priority
?
411 Views
Last Modified: 2014-01-04
I have the following 2 codes assigned to a button in excel. I'd like them to take effect at row 10. Rows 1 through 9 is for users to enter demographic information and should not be afftected by the code:

Adds a row:

Dim rResult As Long
 rResult = MsgBox("This action will copy a row under your selected row, do you want to continue?", vbYesNo + vbQuestion, "Confirmation")
 
  If rResult = vbNo Then
 Exit Sub
End If

ActiveSheet.Unprotect
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.EntireRow.Copy Range("A" & ActiveCell.Row + 1)
ActiveSheet.Protect


Clears Contents from a Row but keeps formulas:

Dim sResult As Long
 sResult = MsgBox("This action will clear your data on the selected row, do you want to continue?", vbYesNo + vbQuestion, "Confirmation")
 
  If sResult = vbNo Then
 Exit Sub
End If
ActiveSheet.Unprotect
Dim rStart As Range, rLast As Range, rConstants As Range

 If Not TypeOf Selection Is Range Then Exit Sub

 Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
 Set rLast = Selection.Rows(1).EntireRow.Cells(1, 42)
 Set rConstants = Nothing

 On Error Resume Next
 Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
 On Error GoTo 0

 If Not rConstants Is Nothing Then
 Call rConstants.ClearContents
 End If
ActiveSheet.Protect

Thank you.
0
Comment
Question by:tracyms
[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
2 Comments
 
LVL 36

Accepted Solution

by:
Kimputer earned 2000 total points
ID: 39756813
In both cases, start the code with

If ActiveCell.Row < 10 Then
    MsgBox "Please don't select row 1 through 9!"
    Exit Sub
End If

Open in new window


It will only display a warning and do NOTHING else. The reasoning behind this, is they clicked the wrong row, so predicting what you think the user meant, is quite risky. Better let the user think clearly, and solve it by letting him click the correct row and clicking the button again.
0
 
LVL 1

Author Comment

by:tracyms
ID: 39756904
Cool. I actually did something similar:

If ActiveCell.Row >= 10 Then
 Dim sResult As Long
 sResult = MsgBox("This action will clear your data on the selected row, do you want to continue?", vbYesNo + vbQuestion, "Confirmation")
 
  If sResult = vbNo Then
 Exit Sub
End If
End If...

but I like your idea better. Thank you!
0

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

715 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