PDA

View Full Version : Solved: Dynamic code based on cell values



bryVA
06-10-2009, 11:00 AM
Hello all,

I am trying to write a macro that will check a sheet called "Bookmark_Map" and place information in a word bookmark based on the cell values. Column A has the bookmark name and Column B has what should be inserted into the bookmark (this maybe a textbox value from a userform or a specific statement). There is a variable amount of bookmarks so how would I code this so when I need to add new bookmarks all I have to do is add them to the sheet instead of in the code.

Right now I have the following code but this doesn't check the sheet.


With oDoc
.Bookmarks("BM_1").Range.Text = ComboBox1.Value
.Bookmarks("BM_2").Range.Text = TextBox1.Value
.Bookmarks("BM_3").Range.Text = TextBox4.Value & ", " & TextBox2 & " " & TextBox3
.Bookmarks("BM_4").Range.Text = Format(Now(), "MMMM D, YYYY")
.Bookmarks("BM_5").Range.Text = "This is done."
.Bookmarks("BM_6").Range.Text = "Please check with me"
.Bookmarks("BM_7").Range.Text = "Don't do that any more"
.Bookmarks("BM_8").Range.Text = TextBox8.Value
End with


So something that say insert Combobox1 (Which is in Range("B3")) in the bookmark that is in Range("A3").

I hope this makes sense.

mdmackillop
06-10-2009, 11:37 AM
Something like this? BkMark is a dynamic range, so add your additional items to the end of the list

Sub Test()
Dim Cel As Range
Dim odoc 'etc.
With odoc
For Each Cel In Range("BkMark")
.Bookmarks(Cel).Range.Text = Cel.Offset(, 1)
Next
End With
End Sub

bryVA
06-10-2009, 11:49 AM
Awesome mdmackillop. If I have say textbox1 in Column B I want the value of the textbox to be inserted into the bookmark can I insert textbox1.value in the cooresponding Column B cell and it will place the value from textbox1 into the bookmark or will this put "textbox1.value" in the bookmark? If not is it possible to do this?

Is it also possible to put an if statement in Column C on some of the bookmarks that are for a checkbox saying if the checkbox is true from the userform then place a string into the Bookmark. I hope this is clear.

Thank you so much,

mdmackillop
06-10-2009, 12:14 PM
You could combine your Userform code with the sheet items. An "x" next items to be written is simplest


Sub Test()
Dim Cel As Range
Dim odoc
With odoc
.Bookmarks("BM_1").Range.Text = ComboBox1.Value
.Bookmarks("BM_2").Range.Text = TextBox1.Value
.Bookmarks("BM_3").Range.Text = TextBox4.Value & ", " & TextBox2 & " " & TextBox3
.Bookmarks("BM_4").Range.Text = Format(Now(), "MMMM D, YYYY")
For Each Cel In Range("BkMark")
If Cel.Offset(, 2) = "x" Then
.Bookmarks(Cel).Range.Text = Cel.Offset(, 1)
End If
Next
End With
End Sub