按钮从用户输入生成列，但不生成单元格行？ [英] Button Generates the columns from user input but not the cell lines?

问题描述

我实现了一个按钮，要求用户在哪里添加列，按钮会占用用户输入（AZ）并生成列，直到表 NOT SPREADSHEET 结束为止。该列根据表中有多少行结束，这意味着如果有10行，则在用户单击按钮后，需要输入列的位置（他们输入列AZ的字母），我不应该请参阅电子表格第11行的列框。

现在我设法做到这一点我的问题如下：

我的问题是单元格的按钮生成不包括单元格周围的行或框，以便您知道它的表的扩展名？

这是我的意思：电子表格图片< a>

注意我的列在单元格周围没有行？

这里是代码，我觉得在
shift：= xlRight之后我缺少一个复制功能，但是我不知道如何实现它？

我不想使用宏，因为由于用户输入的行和列的更改，我将不得不将范围硬编码到宏中我不想要
相信我，我尝试了它的烦人。

  Private Sub CommandButton2_Click（）
 Dim x As Variant 
 Dim ColumnNum 
 
x = InputBox（输入要添加的列：，什么列？）
如果x =然后退出Sub 
 ColumnNum = x 
 
 ThisWorkbook.Sheets（Sheet1）。列（ColumnNum）.Insert shift：= xlRight 
 
 
 ThisWorkbook.Sheets（Sheet1）。列（ColumnNum）.ClearContents 
 End Sub 
  

解决方案

你可以试试这个：

  Private Sub CommandButton2_Click（）
 Dim colIndex As Variant 
 
 colIndex = Application.InputBox（输入要添加的列：，什么列？，，，，， ，2）'<  -  |强制一个文本
如果colIndex =然后退出Sub 
与ThisWorkbook.Sheets（Sheet1）。列（colIndex）'<  -  |您要插入的参考列
 .Insert shift：= xlRight'<  -  |插入一个新列，然后引用的一个将一列移动到插入的一个的右边。
 .Offset（，-2）.Copy'<  -  |将列的两列复制到引用的左边（即新的列左侧）
 .Offset（，-1）.PasteSpecial xlPasteFormats'<  -  |将格式粘贴到新列
 Application.CutCopyMode = False 
结束
 End Sub 
  

Hi I am new to excel 2013 vba programming so bare with me.

I implemented a button that ask the user where to add a column, and the button takes the user input(A-Z) and generates the column until the end of the table NOT SPREADSHEET. The column ends based on how many rows there are in my table, meaning if there are 10 rows, after the user clicks the button an inputs where they want the column to be(They input a letter of the column A-Z), I should not see a column box on line 11 of the spreadsheet.

Now I've managed to do this my issue is below:

My issue is the cells the button generate does not include the lines or boxes around the cells so that you are aware that its an extension of the table?

here is what I mean: Picture of spreadsheet

notice the i column there are no lines around the cells?

Here is code, I think I am missing a copy function after the line shift:=xlRight, but I don't know how to implement it?

I don't want to use macros because since the tables rows and column change due to the user's input I will have to constantly hard-code the range into the macro which i dont want. Trust me I tried it an its annoying.

Private Sub CommandButton2_Click()
Dim x As Variant
Dim ColumnNum

x = InputBox("Enter a column that you want to add: ", "What column?")
If x = "" Then Exit Sub
ColumnNum = x

ThisWorkbook.Sheets("Sheet1").Columns(ColumnNum).Insert shift:=xlRight


ThisWorkbook.Sheets("Sheet1").Columns(ColumnNum).ClearContents
End Sub

解决方案

you could try this:

Private Sub CommandButton2_Click()
    Dim colIndex As Variant

    colIndex = Application.InputBox("Enter a column that you want to add: ", "What column?", , , , , , 2) '<--| force a text
    If colIndex = "" Then Exit Sub
    With ThisWorkbook.Sheets("Sheet1").Columns(colIndex) '<--| reference column you want to insert
        .Insert shift:=xlRight '<--| insert a new column , then the referenced one shifts one column to the right of the inserted one 
        .Offset(, -2).Copy '<--| copy the column two columns to the left of the referenced one (i.e. one column left of the new one)
        .Offset(, -1).PasteSpecial xlPasteFormats '<--| paste formats to the new column 
        Application.CutCopyMode = False
    End With
End Sub

这篇关于按钮从用户输入生成列，但不生成单元格行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！