Styjun

The relationship of noch nicht and the passive voice

Madrid to London w/ Expired 90/180 days stay as US citizen

Template specialization vs. Function overloading

When is the earliest in Earth history when Grade 5 Titanium alloy could be made, assuming one knows the formula and has the feedstock?

How do I write this symbol in latex? (disjoint sharp operator)

Algorithm for competing cells of 0s and 1s

Find all files in directories named foo

Do the villains know Batman has no superpowers?

Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?

How do rulers get rich from war?

What to do as a player when ranger animal companion dies

Are lay articles good enough to be the main source of information for PhD research?

How often is duct tape used during crewed space missions?

Manager manipulates my leaves, what's in it for him?

What are the end bytes of *.docx file format

Integrability of log of distance function

As an employer, can I compel my employees to vote?

What did the controller say during my approach to land (audio clip)?

Is a global DNS record a security risk for phpMyAdmin?

Can I separate garlic into cloves for storage?

How could artificial intelligence harm us?

Is there a connection between IT and Ghostbusters?

Why are Fuji lenses more expensive than others?

Why can't we use uninitialized local variable to access static content of its type?

VBA / Excel: How to allow user to add new line using a dropdown choice

How to avoid using Select in Excel VBAVBA - lining up data values in excelCopying Rows from Worksheet to End of New Worksheet Excel VBAExcel Vba: Creating loop that checks if the values in column A matches and copy all the rows to a new spreadsheetHow to make particular rows in Excel spreadsheet read-only via VBA Macro?Specifying User Permissions in VBA for Protected Excel SheetExcel VBA - How to add new row at the end of a sheet only if the new row differs from the last row?Excel list, VBA add from cellsHow to use index match formula in VBA excelHow to Add New Rows and Fill with “0” in VBA?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

-2

I have created a simple request form that brings user to a spreadsheet where they are asked to fill out First Name, Last Name, User's Alias, Access to Deactivate and Role to Deactivate. The form could be used for up to 9 different users. I'd like to include another column saying "New Line for the Same User?" where a drop-down menu with answers "yes" and "no" would be available. And, if user select "yes", a new line would populate with the same Name and Alias like the row before, while other rows below it would move down by one row. The thing is I'd like to:

1) create a new line, but

2) copy only First Name (Column A), Last Name (Column B) and User's Alias (Column C) of that user. The "Access to Deactivate" option (column D) and the "Role to Deactivate" option would remain empty so the requester can fill them out. And the "New Line for the Same User" option would be not filled out as well in the new, copied row.

Here's a screenshot of what I have in mind:

I've tried different approaches using VBA but since I am not that experienced, I couldn't come up with anything working exactly for the need. I believe it should be a dynamic-range-based so it works throughout the form. Could you please advise?

excel vba

share|improve this question

edited Mar 28 at 14:48

Pᴇʜ

32.2k66 gold badges3030 silver badges5656 bronze badges

asked Mar 28 at 14:31

lewin84lewin84

111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is somehow easy, but because this is not a free code web, you should show us what you got. Please read this on how to ask
  
  – Damian
  Mar 28 at 14:34
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Please post the code you've tried so far. If you're just looking for advice on how to get started, look into the Worksheet_Change event. Info about it here and here.
  
  – tigeravatar
  Mar 28 at 14:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Split your task into logical steps and write those steps down. They should not be divisible any further and follow one after another. Then google each step using "vba STEPNAME example", and you will find lots of code. Try out that code. If you get stuck on a specific spot within the found code, update your question with the code at hand and a specific descritpion of the problem. We will be happy to help you there. We will not write code for you, but help you with specific problems.
  
  – David G
  Mar 28 at 14:40
  
  

  
  
  
  

add a comment
 | 

-2

I have created a simple request form that brings user to a spreadsheet where they are asked to fill out First Name, Last Name, User's Alias, Access to Deactivate and Role to Deactivate. The form could be used for up to 9 different users. I'd like to include another column saying "New Line for the Same User?" where a drop-down menu with answers "yes" and "no" would be available. And, if user select "yes", a new line would populate with the same Name and Alias like the row before, while other rows below it would move down by one row. The thing is I'd like to:

1) create a new line, but

2) copy only First Name (Column A), Last Name (Column B) and User's Alias (Column C) of that user. The "Access to Deactivate" option (column D) and the "Role to Deactivate" option would remain empty so the requester can fill them out. And the "New Line for the Same User" option would be not filled out as well in the new, copied row.

Here's a screenshot of what I have in mind:

I've tried different approaches using VBA but since I am not that experienced, I couldn't come up with anything working exactly for the need. I believe it should be a dynamic-range-based so it works throughout the form. Could you please advise?

excel vba

share|improve this question

edited Mar 28 at 14:48

Pᴇʜ

32.2k66 gold badges3030 silver badges5656 bronze badges

asked Mar 28 at 14:31

lewin84lewin84

111 bronze badge

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This is somehow easy, but because this is not a free code web, you should show us what you got. Please read this on how to ask
  
  – Damian
  Mar 28 at 14:34
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Please post the code you've tried so far. If you're just looking for advice on how to get started, look into the Worksheet_Change event. Info about it here and here.
  
  – tigeravatar
  Mar 28 at 14:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Split your task into logical steps and write those steps down. They should not be divisible any further and follow one after another. Then google each step using "vba STEPNAME example", and you will find lots of code. Try out that code. If you get stuck on a specific spot within the found code, update your question with the code at hand and a specific descritpion of the problem. We will be happy to help you there. We will not write code for you, but help you with specific problems.
  
  – David G
  Mar 28 at 14:40
  
  

  
  
  
  

add a comment
 | 

I have created a simple request form that brings user to a spreadsheet where they are asked to fill out First Name, Last Name, User's Alias, Access to Deactivate and Role to Deactivate. The form could be used for up to 9 different users. I'd like to include another column saying "New Line for the Same User?" where a drop-down menu with answers "yes" and "no" would be available. And, if user select "yes", a new line would populate with the same Name and Alias like the row before, while other rows below it would move down by one row. The thing is I'd like to:

1) create a new line, but

2) copy only First Name (Column A), Last Name (Column B) and User's Alias (Column C) of that user. The "Access to Deactivate" option (column D) and the "Role to Deactivate" option would remain empty so the requester can fill them out. And the "New Line for the Same User" option would be not filled out as well in the new, copied row.

Here's a screenshot of what I have in mind:

I've tried different approaches using VBA but since I am not that experienced, I couldn't come up with anything working exactly for the need. I believe it should be a dynamic-range-based so it works throughout the form. Could you please advise?

excel vba

share|improve this question

edited Mar 28 at 14:48

Pᴇʜ

32.2k66 gold badges3030 silver badges5656 bronze badges

asked Mar 28 at 14:31

lewin84lewin84

111 bronze badge

share|improve this question

edited Mar 28 at 14:48

Pᴇʜ

32.2k66 gold badges3030 silver badges5656 bronze badges

asked Mar 28 at 14:31

lewin84lewin84

111 bronze badge

share|improve this question

edited Mar 28 at 14:48

Pᴇʜ

32.2k66 gold badges3030 silver badges5656 bronze badges

asked Mar 28 at 14:31

lewin84lewin84

111 bronze badge

edited Mar 28 at 14:48

Pᴇʜ

32.2k66 gold badges3030 silver badges5656 bronze badges

edited Mar 28 at 14:48

Pᴇʜ

32.2k66 gold badges3030 silver badges5656 bronze badges

asked Mar 28 at 14:31

lewin84lewin84

111 bronze badge

asked Mar 28 at 14:31

lewin84lewin84

111 bronze badge

1 Answer
1

active

oldest

votes

0

You could try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim LastRow As Long

 With ThisWorkbook.Worksheets("Sheet1")

 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

 If Not Intersect(Target, .Range("F7:F" & LastRow)) Is Nothing And Target.Value = "Yes" Then
 Application.EnableEvents = False
 .Rows(Target.Row + 1).EntireRow.Insert
 .Range("A" & Target.Row & ":C" & Target.Row).Copy .Range("A" & Target.Row + 1 & ":C" & Target.Row + 1)
 Application.EnableEvents = True
 End If

 End With

End Sub

share|improve this answer

answered Mar 28 at 14:50

Error 1004Error 1004

5,57822 gold badges1010 silver badges2525 bronze badges

add a comment
 | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55400175%2fvba-excel-how-to-allow-user-to-add-new-line-using-a-dropdown-choice%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

You could try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim LastRow As Long

 With ThisWorkbook.Worksheets("Sheet1")

 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

 If Not Intersect(Target, .Range("F7:F" & LastRow)) Is Nothing And Target.Value = "Yes" Then
 Application.EnableEvents = False
 .Rows(Target.Row + 1).EntireRow.Insert
 .Range("A" & Target.Row & ":C" & Target.Row).Copy .Range("A" & Target.Row + 1 & ":C" & Target.Row + 1)
 Application.EnableEvents = True
 End If

 End With

End Sub

share|improve this answer

answered Mar 28 at 14:50

Error 1004Error 1004

5,57822 gold badges1010 silver badges2525 bronze badges

add a comment
 | 

0

You could try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim LastRow As Long

 With ThisWorkbook.Worksheets("Sheet1")

 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

 If Not Intersect(Target, .Range("F7:F" & LastRow)) Is Nothing And Target.Value = "Yes" Then
 Application.EnableEvents = False
 .Rows(Target.Row + 1).EntireRow.Insert
 .Range("A" & Target.Row & ":C" & Target.Row).Copy .Range("A" & Target.Row + 1 & ":C" & Target.Row + 1)
 Application.EnableEvents = True
 End If

 End With

End Sub

share|improve this answer

answered Mar 28 at 14:50

Error 1004Error 1004

5,57822 gold badges1010 silver badges2525 bronze badges

add a comment
 | 

You could try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim LastRow As Long

 With ThisWorkbook.Worksheets("Sheet1")

 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

 If Not Intersect(Target, .Range("F7:F" & LastRow)) Is Nothing And Target.Value = "Yes" Then
 Application.EnableEvents = False
 .Rows(Target.Row + 1).EntireRow.Insert
 .Range("A" & Target.Row & ":C" & Target.Row).Copy .Range("A" & Target.Row + 1 & ":C" & Target.Row + 1)
 Application.EnableEvents = True
 End If

 End With

End Sub

share|improve this answer

answered Mar 28 at 14:50

Error 1004Error 1004

5,57822 gold badges1010 silver badges2525 bronze badges

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

 Dim LastRow As Long

 With ThisWorkbook.Worksheets("Sheet1")

 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

 If Not Intersect(Target, .Range("F7:F" & LastRow)) Is Nothing And Target.Value = "Yes" Then
 Application.EnableEvents = False
 .Rows(Target.Row + 1).EntireRow.Insert
 .Range("A" & Target.Row & ":C" & Target.Row).Copy .Range("A" & Target.Row + 1 & ":C" & Target.Row + 1)
 Application.EnableEvents = True
 End If

 End With

End Sub

share|improve this answer

answered Mar 28 at 14:50

Error 1004Error 1004

5,57822 gold badges1010 silver badges2525 bronze badges

answered Mar 28 at 14:50

Error 1004Error 1004

5,57822 gold badges1010 silver badges2525 bronze badges

answered Mar 28 at 14:50

Error 1004Error 1004

5,57822 gold badges1010 silver badges2525 bronze badges