Search code examples
vbaexceltextcell

(Excel VBA) - Draw from cell text in a macro

I'm trying to build a small macro that allows the user to format multiple different documents at once.

I would like for the user to be able to enter into a particular cell within the document containing the macro a particular piece of text.

I then want for this piece of text to be able to be drawn upon in the macro while affecting a different document.

For instance, a code to add another column might say

Worksheets(1).Range("A1").EntireColumn.Insert

Instead of specifying the column (A), I would like it to draw on a value in the host document. For instance, the user types "G" into the particular cell, and then clicks a button to run the macro, and the macro will dynamically know to affect column G in all excel documents it targets based off of the value in the host document.

I hope this makes sense.

Any suggestions for the sort of functions I should be looking at to make this work?

Solution

  • "Any suggestions on the sort of functions I should be looking at?" Here's a few...

    To get the value which is entered...

    If the cell will always be in the same address, say A1:

    ' Define a string variable and set it equal to value in A1
Dim cellText as String
cellText = ThisWorkbook.ActiveSheet.Range("A1").Value

    or instead of using Range you can also use Cells which takes a row and column number.

    cellText = ThisWorkbook.ActiveSheet.Cells(1, 1).Value

    If the cell changes then you may need to look into the Find function to look for a label/heading next to the input cell. Then you can use it (easily with Cells) to reference the input...

    Once you have this variable, you can do what you like with it.

    To put this value into cell B3 in another (open) workbook named "MyWorkbook", and a sheet named "MySheet" you can do:

    Application.Workbooks("MyWorkbook").Sheets("MySheet").Range("B3").Value = cellText

    To insert a column at cellText, do 

    Application.Workbooks("MyWorkbook").Sheets("MySheet").Range(cellText & "1").EntireColumn.Insert

    Notably here, the & concatonates the strings together, so if

    cellText="B"

    then

    cellText & "1" = "B1"

    Further to your comment about moving values between sheets, see my first example here, but always refer to the same workbook. If you find yourself repeatedly typing something like ThisWorkbook.Sheets("MySheet").<other stuff>

    then you can use the With shorthand. 

    With ThisWorkbook.Sheets("MySheet")

    ' Starting anything with a dot "." now assumes the with statement first
    .Range("A1").Value = .Range("A2").Value
    .Range("B1").Value = .Range("B2").Value

End With

    Important to note is that this code has no data validation to check the cell's value before using it! Simply trying to insert a column based on a value which could be anything is sure to make the macro crash within its first real world use!