
こんばんは。
お世話になります。
エクセルVBAにてアクセスからデータを取得し、その一部をロックしたいです。
(取得方法には色々あるかと思います。
経験が浅いためどう表現すれば適切か自信がありませんが
「ADOコネクションオブジェクトとADOレコードセットオブジェクトにて実施しています。」)
【VBAの仕様の説明】
「読込」ボタンを押すと、B12~AA1000のエリアのデータを一掃して、
そこに条件によりレコード数が変わりますが、アクセスのデータをセットしています。
その後、これらの出力されたレコードについてエクセル上にて値を書き換えたのちに
「更新」ボタンを押すと、アクセスに更新に行くというものです。
このときB,C,D,E列については、更新処理時に重要なものであり、F列以降と異なり
書き換えてはいけないものです。
【実現したいこと】
このエクセルを開いてから閉じるまでの間、いつでもB12~E1000は手入力不可にしたいです。
ただし、エクセルを開いていきなりロックをしてしまうと、「読込」を押したときに
アクセスのデータを出力するときにエラーになってしまいますので
読込ボタンを押した後はB12~E1000のロックを外したいです。
これが難しいようであれば、エクセルを開いてから「読込」を押すまでの間は
ロックをかけなくてもよいです。
ある程度ググったので
シートを保護する & 特定のセルのLOCKをfalseにする を適切なタイミングで
実施するのだとは理解していますが、実装しようとすると
「RangeクラスのLockedプロパティを設定できません」というエラーが出てしまって
詰まってしまっています。
よろしくお願いいたします。
↓↓↓↓ソースです。↓↓↓↓
Private adoCn As Object 'ADOコネクションオブジェクト
Private adoRs As Object 'ADOレコードセットオブジェクト
Private strSQL As String 'SQL文
Private Const DBpath As String = "C:\zaiko.accdb" '接続するファイル(2007~)のフルパス
'---------ファイルが開けない場合のエラーを追加
Private file_error As String
'---------
Sub DBconnect(flg As Boolean) 'DB接続プロシージャ
'---------ファイルが開けない場合のエラーを追加
On Error GoTo Err_Handler 'エラーが起きたら"Err_Handler"へ
file_error = 0 'エラーが起きない正常な間はエラーをオフにする。
'---------
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
If flg = True Then Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成
'adoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBpath & ";" 'Accessファイル(~2003)を開く
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBpath & ";" 'Accessファイル(2007~)を開く
'---------ファイルが開けない場合のエラーを追加
Exit Sub '正常ならここで終了
Err_Handler: 'エラーが起きたらここへ飛ぶ
MsgBox "「C:\」フォルダの下にエクセルとアクセスファイルを置いてください。"
file_error = 1
'---------
End Sub
Sub DBcut_off(flg As Boolean) 'DB切断プロシージャ
If flg = True Then adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ
Set adoRs = Nothing 'オブジェクトの破棄
Set adoCn = Nothing
End Sub
Sub DBread() '読み込み
Dim shouhinbangou As String, dy As String, txt As String
Call DBconnect(True) 'DB接続
If file_error = 1 Then
file_eroor = 0 '初期化してから
Exit Sub '処理終了
End If
With UserForm1
.show 'ユーザーフォーム表示
If .TextBox1 = "" Then '商品番号欄が空欄の場合
shouhinbangou = ""
Else '商品番号欄が記入済
shouhinbangou = "WHERE item_no LIKE '%" & .TextBox1 & "%' " '~を含む
End If
End With
strSQL = _
"SELECT * " & _
"FROM zaiko_table " & _
shouhinbangou
adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
Range("B12:Z1000").ClearContents '前のデータクリア
Range("B12:Z1000").Font.ColorIndex = xlAutomatic 'フォント色を初期化
Range("B12:AA1000").Borders.LineStyle = xlLineStyleNone
Application.EnableEvents = False 'イベントオフ(ワークシートチェンジが反応しないように)
i = 12 'スタート行
Do Until adoRs.EOF 'レコードセットが終了するまで処理を繰り返す
Cells(i, 2) = adoRs!ID
Cells(i, 3) = adoRs!item_no
Cells(i, 4) = adoRs!color_no
Cells(i, 5) = adoRs!item_name
Cells(i, 6) = adoRs!~~~
~中略~
Cells(i, 26) = adoRs!~~~
i = i + 1 '行をカウントアップする
adoRs.MoveNext '次のレコードに移動する
Loop
'下から数える
With Range("B12")
.Resize(Cells(Rows.Count, .Column).End(xlUp).Row - .Row + 1, 26).Borders.LineStyle = xlContinuous
End With
Application.EnableEvents = True 'イベントオン
Call DBcut_off(True) 'DB切断
End Sub
No.3ベストアンサー
- 回答日時:
> 赤字化されているセルの値をアクセスに更新する仕様なのですが、
> この赤字化するモジュール内でエラーになってしまいます。
試してみました。
保護を有効にするとき、デフォルトでは書式の変更が許可されていないためと思われます。
ActiveSheet.Protect AllowFormattingCells:=True
のように許可してやればエラーが消えました。
ほかにも色々オプションがありますので、Worksheet.Protect のヘルプを見て仕様に合うよう調整してください。
参考URL:http://msdn.microsoft.com/ja-jp/library/ff840611 …
ありがとうございます!
出来ました。
cellsとrangeの影響等は無関係でしたね。
調べる方向性がまったく違っていてうまく目的に到達できませんでした。
大変助かりました、ありがとうございます。
参考URLも見ました。
役に立つサイトをありがとうございます。
No.2
- 回答日時:
#1 です。
ごめんなさい。
当方Excel2010ですが、VBAでシート保護を行うと
パスワードの設定はしていないにもかかわらず、
UnProtect しようとすると、パスワードを要求される事態に。。。
?????????????????????????????
そちらのエラーの原因はWorksheet.Protect メソッドのパラメータが適切でないため?
のような気がしますけれども、
申し訳ありません、こちらを閉じて、再度質問を立て直して
あらまほしき先達の方のご登場をおまちください。
すみません。
おはようございます。
ご連絡ありがとうございます。
nicotinismさまの状況はまた私のものとは
違うようですね。。
この質問は閉じないこととしようと思います。
実現したいことができていないことと
nicotinismさまとのやりとりから私の最新の
状況がお伝えできるかと思うためです。
どなたか、ご回答よろしくお願いいたします。
nicotinismさま、改めて迅速なご回答、
そして最新の私の状況にもご対応を試みて下さり
ありがとうございました。感謝しています。
No.1
- 回答日時:
>実装しようとすると
>「RangeクラスのLockedプロパティを設定できません」というエラーが出てしまって
問題を切り分けるために別の適当なファイルで
ロック・アンロックのSub モジュール2個だけをつくって試されては?
RangeクラスのLockedプロパティを設定できません
でGoogleとシート保護・セルのロックの順番が違うとダメとか
連結セルが・・
とかヒットしますけど。。
ありがとうございます。
subモジュール2個で試してみました。
そのテストはうまく行ったので
ロック処理とアンロック処理の記述を
上記ソースの先頭と末尾にセットしました。
先頭
ActiveSheet.Unprotect '//シート保護を解除
末尾
Range("F12:Z1000").Locked = False '//在庫数部分のみアンロック
Range("AB12:AB1000").Locked = False '//備考部分のみアンロック
ActiveSheet.Protect '//シート保護
ほぼ成功したのですが、上記adoRsでデータセットしている部分のセルの数値を変更すると、そのセルを赤字化し、
赤字化されているセルの値をアクセスに更新する仕様なのですが、この赤字化するモジュール内でエラーになってしまいます。
エラー箇所は以下で
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Long
c = Target.Column '変更されたセルの列
r = Target.Row '変更されたセルの行
If c < 6 Or 26 < c Or r < 12 Or 100 < r Then Exit Sub '範囲外なら終了
Cells(r, c).Font.ColorIndex = 3 'セルを赤文字に【ココ】
End Sub
エラー内容は以下です。
「アプリケーション定義またはオブジェクト定義のエラーです。」
RangeとCellsはよく一緒に見かけるのですが、
今回セルのロックにRangeを使っていることにより
この赤字化のCellsの部分に影響を及ぼしているのではないかと推測しています。
状況と解決策がお分かりでしたらよろしくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ExcelからAccessのテーブルに書き込む時に時間がかかる 1 2022/10/14 20:38
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Visual Basic(VBA) 【追加】ファイルを閉じてダイアログで保存した時だけ処理の実行をする 3 2022/03/23 15:43
- Excel(エクセル) エクセルVBAでオブジェクトが必要です 2 2022/09/10 16:37
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
- Visual Basic(VBA) InputBoxでキャンセルボタンを押したらファイル自体を閉じたい 3 2022/07/23 17:52
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- Visual Basic(VBA) VBA 請求書自動作成 3 2022/04/24 01:58
- Visual Basic(VBA) 【Excel VBA】自動メール送信の機能追加 5 2022/09/29 12:53
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・「これはヤバかったな」という遅刻エピソード
- ・初めて自分の家と他人の家が違う、と意識した時
- ・いちばん失敗した人決定戦
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
PowerShellを使って関連付けら...
-
ExcelVBAで既に開いてるwordを...
-
access テキストボックスの値取得
-
VB6 Dir関数で52エラー発生
-
VBから参照できないCのDLLを使...
-
NAS上のファイルの使用中が解除...
-
アクセスのクエリでコンパイル...
-
Returnに対するGoSubがありません
-
batファイルでレジストリキーの...
-
すでにファイルが開かれている...
-
ファイルをクローズしたい
-
DBへのコネクションタイムアウト
-
VB:「実行時エラー53 実行ファ...
-
インストール時のエラーメッセ...
-
VBAのChangeFileOpenDirectory...
-
Adobeのプレミアプロの書き出し...
-
「パス名が無効です」の発生原因
-
インプットボックスからファイ...
-
FatFsでのエラーについて
-
AutoExecで自動終了させるには?
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
access テキストボックスの値取得
-
PowerShellを使って関連付けら...
-
エクセルマクロでエラーの原因...
-
「パス名が無効です」の発生原因
-
Returnに対するGoSubがありません
-
NAS上のファイルの使用中が解除...
-
VB6 Dir関数で52エラー発生
-
アクセスのクエリでコンパイル...
-
すでにファイルが開かれている...
-
batファイルでレジストリキーの...
-
FTPの送信結果を検知したい
-
ExcelVBAで既に開いてるwordを...
-
VBでファイルが開かれているか...
-
ACCESS VBAでのインポート
-
VBから参照できないCのDLLを使...
-
【COBOL】read文でエラー
-
gccを行ってもexeファイルが生...
-
fgets関数のEOFの扱い方について
-
EXCELのVBAでWORDが開いてある...
-
Excel 2003 のエラーメッセージ
おすすめ情報